DataBase - Table Design with Many Fields

Asked By JD McLeod

08-Feb-10 12:18 AM

I am using Access 2007.  I have a table ACCOUNTS, with fields for account
number, account description, and account balance.  There are 12 other fields
for various assertions/characteristics related to the accounts.  Each account
will have at least one of the 12 assertions apply, and many will have more
than one.  Each characteristic has its own field.  I have a form that is used
to enter all of the account information including combo boxes to select
either high, mod, or low as the value for each of the other 12 fields.

Next, I have to determine whether or not each account is significant and
what business processes are related to it.  I thought of using another table
for this.  For example, the table ACCOUNTS2 would pull in the account
information from the first table and then have a field for 5 questions that
must be answered in determining whether or not the account is significant.
In addition, the user needs to identify all business processes related to
this account.  For example, if the account were Accounts Payable, the
processes might include Vendor Setup, Invoice Approval and Check Signing.
All accounts will have at least one process, but many will have more than
one.  I thought about doing it all in one table, but was concerned about
having too many fields.

I need help designing this table.  I don???t think the way I am doing it right
now is correct.  Can any of this be done in a form and not saved in a table?
Can you create reports and queries off of data in forms?  Any advice would be
helpful.  Thanks.

08-Feb-10 03:58 AM

Perhaps these 5 tables would do what you need:

a) AccountType: one record for each type of account, with AccountTypeID
primary key.

b) Process: one record for each type of process you may need to handle, with
ProcessID primary key.

c) AccountTypeProcess: One record for each combination of account type and
process. (For example, if the "Accounts Payable" has 5 processes associated
with it, there will be 5 *records* in this table for that account type.)

d) Account: one record for each account, with an AccountID primary key, and
AccountTypeID foreign key.

e) AccountProcess: one record for each process associated with this account.
So, if it is an "Accounts Payable" type, it will have 5 records to cover each
of the processes.

With table (c) above, you have taught your database what processes are
associated with each type of account. So, when you enter a new account into
(d), you can use the AfterInsert event of the form to Execute an Append
query statement to append the appropriate records automatically into table
(e.)

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

08-Feb-10 09:57 AM

Thanks, Allen, I will give this a try.  Can you explain what is meant by
not sure what is the foreign key.  thanks again.

08-Feb-10 11:20 AM

You would have:

TblAccountType
AccountTypeID
etc

TblAccount
AccountID
Account
AccountTypeID
etc

AccountTypeID is the primary key in TblAccountType and AccountTypeID is the
foreign key in TblAccount. A foreign key relates a record in some table to a
record in another table. In your case,  AccountTypeID identifies what
AccountType each Account in TblAccount is.

Steve
santus@penn.com

08-Feb-10 07:51 PM

Primary key is a unique identifier in one table.

Foreign key is the matching field in a related table. it is not unique
(typically it is the many side of a one-to-many relation), but it relates to
the primary key of another table.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

08-Feb-10 07:57 PM

Thanks Allen & Steve
Since I am still new to Access, I decided to start small and see if i could
understand how to do this or something like it.  I created two tables, one
for Acct and one for Process.  Then i created a third table,AcctProcess,
which has a field for the account and the process.  I used a form with combo
boxes to lookup the values in the Acct and Process tables and match them and
then fill in the values in table three.  In table three, it stores the
primary ID for each the account and processs.  If i have 4 processes for
AcctsPayable, then i have 4 separate records in the AcctProcess table.  Is
this a way to do it and still have a "normal" data structure to the table.

I got to thinking, that there are only 4 types of accounts and an account
can only have one type assigned to it, so i just made that a field in the
Acct table and used the data entry form to restrict what the users could
enter.  I figured that would eliminate the need for a separate table.  I look
forward to your thoughts.

09-Feb-10 04:16 AM

Beautiful: what you have built may be all you need.

You can now create a query combining the 3 tables.
It will give you each process for each account.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.