It looks like edward is putting in some good comments. I will try and place
some comments within the text below as seems appropriate.
I'm not sure how the below pasted information is going to appear but I could
not find a way to attach my spreadsheet showing the various fields (and
identical field names in parentheses) along with the 3-character high risk
customer types across the top. The FTN group information needs have not been
determined yet but they will be similar to the others. I included the 1st,
2nd, and 3rd review dates in the same table as that is what they wanted.
First of all, remember that a database is not a spreadsheet. Your tables
appear to have columns and rows like a spreadsheet, but they do not
correspond to each other. So, when you have fields that are labeled 1st
review date, 2nd review date, and 3rd review date, you are using fields that
are not unique. The data is unique, but the fields are not. They are all
review dates. And in the future, if someone (like a manager or congress)
decides that you need a 4th review date, you have to go in and redo your
tables, queries, forms and reports. If you have fields that are not unique,
then that data should be placed into a separate table, which has a field for
a review date, and a field for the foreign key that identifies which account
it is related to. You will not be looking at tables to analyze your data,
you will be using queries/forms/reports for that. This is not a spreadsheet.
Not all customer types will, of course, have data for all fields. The
ActiveList (Category) field will contain the 3 character (FTN, NRA, SAR,
etc.) risk type and for those customers who fit into more than one category,
the OtherLists field will contain that data.
Again, this is not a spreadsheet. If there can be more than one risk type
for a customer (and I notice that you are speaking about customers here,
rather than accounts), then it would be appropriate to have a table that
lists all of the risk types, and then have a linking table where you can link
any customer with any number of risk types.
In addition to some customers having more than one account they may also
have more than one customer number and some will have both a SS# and another
taxpayer identification number. I believe those additional numbers can be
stored in fields similar to the ActiveList/OtherLists data.
Have a table that links with a foreign key to the customer table, and have a
field for all identifying information, with another field to describe that
information. Then you can link the customer to any number of identifications.
If you need more information please let me know.
Thank you again, mnature (you're not Mother Nature are you?).
It's not nice to fool Mother Nature!
FTN NRA SAR CIB HRI OLB PCS HRW
CustID (ID#) (can a case refer to more than one CustID?)
Case#
CustomerName (CaseName)
AKAName (separate table? could be a lot of names here. Or would this be
part of a customer's account data)
IDDoc (can a case refer to more than one document?)
IDType (can there be more than one type per case or customer? does this
refer to customer or case?)
AdditionalSuspects (if there can be more than one, then you may consider
having a list of names, some of which can be customers, some suspects, and
some both)
CustomerNumber (different than customer ID?)
CustomerTIN (SSN-TID) (TaxID)
City
St
HIFCA/HIDTA?
Country
HighRiskCountry? (Don't need this here. What if the high risk counties
changes? Instead, use a query to find which customers/cases belong to high
risk countries)
EntityType
ActiveList (Category) (Place this in a separate table. Can a customer/case
belong to more than one category?)
OtherLists (no, no. Always have a place for everything, and everything in
it's place. Otherwise, you will work too hard trying to sort out your data)
EntityType
CIPInfoOnFile?
ExemptionStatus
AccountNumbers (are these other account numbers for a particular customer?
This is not needed, because you can find this out from a query on that
customer)
TypeOfAccount
Status
Customer?
IsUSBusiness?
HighRiskBusiness?
PubliclyTraded?
SuspectedMSB?
ConfirmedMSB?
SARFiled?
SARStatus
DateSARSup
EIRSubmitted
InInterimEDD?
CashVolume
WireActivity (IntWires?)
InternationalTrans (IntWires) (InternationalWires)
CountryOfWire
Wire
InternetSearch (Sounds like this could happen repeatedly, good to go into a
separate table to keep track of dates and notes)
CustomerTypeOfBusiness
BeneficiaryOriginator
BeneficiaryTypeOfBusiness
AcctReviewed (Sounds like you need a table to track reviews)
RiskCategory (RiskRating) (can there be more than one per customer?)
IntensiveCash (one time, or multiple times?)
NextReviewDate (This should not be a field. This should be derived from the
last review date, using a query)
FirstReview (Just have reviews. Put them into a table, referring over to
the account or customer ID, and sort them by date. The latest one can be
pulled out using a query, and the next review date calculated from that date)
SecondReview
ThirdReview
Notes (A good idea to use a notes field with many of the tables where you
have a series of entries. You can then keep a running commentary of what is
happening, and then view a series of dates or audits, along with the notes,
to get a better view of what is really happening.)
I get the feeling that you have been juggling the need for information with
the lack of flexibility of Excel. I believe that Access can help you a lot
with what you are doing, but you will have to give up doing a lot of the work
by hand, and allow Access to do most of the work for you. It could be very
helpful, at this time, to step back and look at what you really need to do
with your data, what types of information would be helpful, and what would be
good to keep track of for future reference. You are not limited by rows and
columns here. If you need to make multiple references, you don't need to
make additional columns.
Figure out what the purpose of your database is (which can include what is
not part of the purpose), and then figure out what tasks you want to perform.
It sounds like a lot of this is mandated for you, but it can still help to
write it out in simple words (such as you would use to explain it to a
manager). Then you look at the data you will be putting into the database,
and organize the data into related groups (such as putting all data about
customers into one group, and all data about accounts into another group.
The two are related, but are still two different groups). This is the
beginning of your table structure. If some data is going to be repeated
(such as risk assessments), then that can be pulled into separate tables, and
referenced through using a key.