Table design

[Lisa Randolph]

I need to track bank statement data. Statements must be grouped in the
following order: by customer, bank, account, statement date, statement data.
I already have a customer table linked to the bank and account tables. I'm
struggling with the best design for the statment table.

Bank statements can contain a wide range of potential fields, which I've
defined in a table of bank codes. For each statement, I need to be able to
input any 1 of over 2000 potential bank codes and store them, along with the
bank fees paid. That would probably be fairly simple to setup, but it gets
worse, however, as not all bank statements provide bank codes. They just use
a written description for the fee. Therefore, I need to provide some method
for entering these fees as well, probably a set of forms for each Family of
bank codes.

My question is - what would be the best design for storing and retrieving
the statement data and can you recommend a solution to the data entry
problems (codes vs no-codes). This is going to be tedious entry so I'd like
to keep it as straight-forward as possible.

MANY THANKS!!

 

[John Vinson]

I need to track bank statement data. Statements must be grouped in the
following order: by customer, bank, account, statement date, statement data.

Tables HAVE NO ORDER. They are unordered "heaps" of data. If you want
to sort information, create a Query sorting it by the desired fields,
and use that Query.

I already have a customer table linked to the bank and account tables. I'm
struggling with the best design for the statment table.

Bank statements can contain a wide range of potential fields, which I've
defined in a table of bank codes. For each statement, I need to be able to
input any 1 of over 2000 potential bank codes and store them, along with the
bank fees paid. That would probably be fairly simple to setup, but it gets
worse, however, as not all bank statements provide bank codes. They just use
a written description for the fee. Therefore, I need to provide some method
for entering these fees as well, probably a set of forms for each Family of
bank codes.

My question is - what would be the best design for storing and retrieving
the statement data and can you recommend a solution to the data entry
problems (codes vs no-codes). This is going to be tedious entry so I'd like
to keep it as straight-forward as possible.

You should have a single field in the Statement table for the FeeType.
You can use a Form (probably a Subform of the Customer form) with a
combo box bound to the FeeType. If you base this Combo on the table of
bank codes, you can select any existing code from the list; but if you
set the Combo's Limit to List property to No, you'll be able to type
in anything you like into the field. If you want to set up your own
translation from text to codes, you'll need a separate form to do so.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps