designing database

[Guest]

I have 6 Access databases and 2 Excel workbooks. Each of these
databases/workbooks contain customer data. Most of the customers have a
customer number (some have more than one). Many of the customers have more
than one account number. Some customers share their account numbers with
other customers (I work for a bank).

My manager wants me to put ALL of these customers into one database and to
relate the account numbers to the applicable customers. I am not sure of the
best way to do this since I do not have a unique number for each customer.

My thinking is that I would create a customer information table and import
all of the customers into it. Than I would let Access assign a number to each
customer and I would have to create a separate account number table and
assign that same number to each account that the customer has. I would also
have a many-to-many relationship since more than one customer may have access
to the same account number(s). There will possibly be over 50,000 customers
and well over 100,000 accounts.

I am not an Access developer and find most of my answers in groups like
this. Your help is greatly appreciated!

David Vollmer

 

[Guest]

Simply speaking (rather an oxymoron when talking about Access) you will need
at least three tables: Customers, Accounts, and CustomerAccounts.

tbl_Customers
CustomerID (PK)

tbl_Accounts
AccountID (PK)

tbl_CustomerAccounts
CustomerAccountID (PK)
CustomerID (FK)
AccountID (FK)

The table for customer accounts allows you to match any customer with any
account. You might want to add a boolean that lets you designate a customer
that is the primary owner of the account.

However, your big problem is going to be transferring your data into this
database. Without knowing what your current data looks like, it is difficult
to give you advice on that. If you could post a little information about
what your databases and workbooks contain, it could be helpful. I would
certainly recommend trying to consolidate all data and dumping it into a new
database all at one time, rather than trying to do it piecemeal, but it would
have to be carefully thought out beforehand.

 

[Guest]

mnature,

Thank you for responding. Since all customers do not have a unique "customer
number" I assume I will be letting Access assign a different one for each
customer and that will be the CustomerID field. In the tbl_Accounts I also
assume that the AccountIDs are all of the individual account numbers. In the
tbl_CustomerAccounts is the CustomerAccountID an autonumber?

I don't understand adding a boolean to let me designate the primary owner of
the account.

When I get to my office tomorrow I will provide you with the information
regarding the current data that I have in the existing databases and
workbooks. One if the workbooks has yet to be created but the data will, I'm
sure, be quite similar to the other groups of customers.

You are right about the hard part being the transferring of the data from
all of the other sources into a single database.

By the way, the purpose of all of this is for us to track the activities of
customers who have been flagged as high risk. Our people are charged with
finding money launderers and terrorist funders so your help is more valuable
than you might think!

Thank you again.

David

 

[Guest]

mnature,

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.

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.

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.

If you need more information please let me know.

Thank you again, mnature (you're not Mother Nature are you?).

David

Field Names for High Risk Tables Used In The Following Tables
FTN NRA SAR CIB HRI OLB PCS HRW
CustID (ID#) Y Y Y
Case# Y
CustomerName (CaseName) Y Y Y Y Y
AKAName Y
IDDoc Y
IDType
AdditionalSuspects Y
CustomerNumber Y Y Y Y Y Y Y
CustomerTIN (SSN-TID) (TaxID) Y Y Y Y Y Y
City Y Y Y
St Y Y Y
HIFCA/HIDTA? Y Y Y
Country Y Y
HighRiskCountry? Y
EntityType Y
ActiveList (Category) Y Y Y Y
OtherLists Y Y Y
EntityType
CIPInfoOnFile? Y
ExemptionStatus Y
AccountNumbers Y Y Y Y Y Y Y
TypeOfAccount Y
Status Y
Customer? Y
IsUSBusiness? Y
HighRiskBusiness? Y
PubliclyTraded? Y
SuspectedMSB? Y
ConfirmedMSB? Y
SARFiled? Y Y Y Y Y
SARStatus Y
DateSARSup Y
EIRSubmitted Y
InInterimEDD? Y Y
CashVolume Y Y Y
WireActivity (IntWires?) Y Y
InternationalTrans (IntWires) (InternationalWires) Y Y Y
CountryOfWire Y
Wire Y
InternetSearch Y
CustomerTypeOfBusiness Y
BeneficiaryOriginator Y
BeneficiaryTypeOfBusiness Y
AcctReviewed Y
RiskCategory (RiskRating) Y Y Y Y Y Y Y
IntensiveCash Y
NextReviewDate Y
FirstReview Y Y Y Y Y Y Y
SecondReview Y Y Y Y Y Y Y
ThirdReview Y Y Y Y Y Y Y
Notes Y Y Y Y Y Y Y

 

[edward]

Since all customers do not have a unique "customer
number" I assume I will be letting Access assign a different one for each
customer and that will be the CustomerID field. In the tbl_Accounts I also
assume that the AccountIDs are all of the individual account numbers. In the
tbl_CustomerAccounts is the CustomerAccountID an autonumber?

I'd have to say it depends. If the customer numbers are unique within
each existing file, you might prefer to add a high-order digit to make
the numbers unique, thus maintaining the old numbers. However, you
mention later that some customers have multiple customer numbers. Is
this because of the multiple current sources? If so, then perhaps the
best way is to assign new numbers (either manually or via autonumber);
since you may need to reference the original numbers, you would
probably import these into fields created just for this
cross-reference. A lot depends on how the human part of the system uses
these numbers -- if the numbers are important to humans, make sure
first that the numbers are convenient for the humans and then figure
out how to make Access present those numbers. And extra field or two in
the database is no biggie compared with the frustration and errors
caused by forcing people to use inconvenient handles.

Of course I'm interpreting what mnature wrote, but yes, I would assume
that account numbers are already unique and need to be maintained, so
you'd probably want to use them as the PK in the new table. If you
added a new number for the PK, you'd end up storing the "real" account
number in a field in the table anyway. OTOH, if there's ANY chance that
you'll ever need to store records with duplicate account numbers --
even if it's just due to a data entry error that can't be immediately
corrected -- then you should do just that (add an autonumber field for
use within the database and store the real account number in a separate
field). The users will never see this autonumber. Given what you say
about the application, you should take this possibility seriously. In
classical banking, you would just say that a duplicate account number
is an error and reject the transaction until it's corrected, but you
may be dealing with situations where time is of the essence and you
cannot afford to reject data which might provide important guidance
just because it doesn't fit the banking rules.

Your mention that some customers have multiple customer numbers raises
a red flag to me. Is this because they are in multiple files or
databases? If so, how do you plan to match them? Are you going to
accept ending up with multiple records for the same person in some
cases? Matching people in different files is a classical problem, and
it's very difficult because it has no purely technical solution. Names
are not much good for matching, since the relationship of people to
names is many-to-many.

I don't understand adding a boolean to let me designate the primary owner of
the account.

I believe that mnature was simply referring to the fact that when
multiple people have access to an account, often one is designated as
the primary owner. This was just a note on application design not
directly related to your inquiry.

You are right about the hard part being the transferring of the data from
all of the other sources into a single database.

One thing I would VERY strongly recommend is that you automate ALL the
steps of the conversion. It's very tempting to just start out fiddling
with this data, correcting that export and this import, etc, figuring
you'll eventually get to the end. But nine times out of ten, you'll
find that you need to go back and repeat some of the previous work
because you learned something important about the data, and unless you
kept extremely detailed notes on every step (which I've never seen),
you'll end up solving the same problems several times. It'll take more
work to do everything you need in scripts and macros and VB, but when
you're done you'll have the total spec in code. When you're done,
you'll just push a button and say "here's the new DB". And when
something fails in the transfer (the best-laid plans of mice and men
and all that), you'll just tweak it and do it again. OK, in this kind
of situation you won't automate it quite that thoroughly, but you
should aim to get it down to a process that takes no more than about
five or ten minutes.

Edward Reid

 

[Guest]

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.

 

[Edward Reid]

AcctReviewed (Sounds like you need a table to track reviews)
Definitely.

NextReviewDate (This should not be a field. This should be derived from the
last review date, using a query)

Probably. But I can imagine these situations:

1) Fixed review interval, for example, "a high risk account will always
be reviewed within a week of the previous review, and the next review
will never be scheduled sooner than a week". In this case, you don't
need a field, since you can just do a query "more than 7 days since
latest review in list"'.

2) Variable review interval, calculated from other data: in this case,
you *could* do it with a query. But if the criteria are complex, it may
be easier to calculate the date after the review and store it. You
could do this with a field in the main table, or by creating the review
record at the time you calculate the date.

3) Review interval is normally fixed (or calculated) but can be changed
by the (human) reviewer. In this case you need a field, which you will
calculate and store when the review is completed, so that it can be
updated manually if needed.

4) Review interval is established by the reviewer. In this case you
need a field. However, you might want to ask for a policy to be
established as to the default date (changing this to case 3).

I suspect that in your type of application, the human reviewers are
likely to be unhappy if they are not allowed to override the computed
next review date.

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)

Note that this is true even if the maximum number of reviews is fixed.

The exception might be if "first review" and "second review" (etc)
aren't really the same -- if they are different kinds of events even
though they are both called "reviews".

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.

I very strongly second this. Just diving in and coding is useful for
learning the capabilities of the product (Access in this case), how to
code, what sorts of things are easy and what are difficult. However,
it's a very poor way of building an application. In fact, the "code
first think later" approach is probably how your group found itself in
the position of having important data in six databases and two
workbooks.

Edward

 

[Guest]

I agree with Edward's comments, a NextReviewDate field should be part of the
Review Table. This serves several purposes: Allowing an auditor to see that
the reviews are done in a timely manner, and to provide for different time
periods before the next review. You could even be tracking different
multiple reviews for each case, if that was needed, in which instance it
would be important to keep the time streams separate. For instance, you
might be reviewing overseas banking records on a periodic basis, but you also
want to review what the local cash flow is, also. It is the sort of thing
that would be a nightmare to attempt in Excel, but is extremely easy to do in
Access.