Table Design with Many Fields

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.

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's 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.


"JD McLeod" wrote in message
...
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.

Thanks, Allen, I will give this a try. Can you explain what is meant by
"foreign key" in item d. of your post. I understand primary key, but i am
not sure what is the foreign key. thanks again.

"Allen Browne" wrote:

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's 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.


"JD McLeod" wrote in message
...
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.

.

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



"JD McLeod" wrote in message
news
Thanks, Allen, I will give this a try. Can you explain what is meant by
"foreign key" in item d. of your post. I understand primary key, but i am
not sure what is the foreign key. thanks again.

"Allen Browne" wrote:

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's 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.


"JD McLeod" wrote in message
...
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.

.

Primary key is a unique identifier in one table.

Foreign key is the matching field in a related table. It's not unique
(typically it's 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.


"JD McLeod" wrote in message
news
Thanks, Allen, I will give this a try. Can you explain what is meant by
"foreign key" in item d. of your post. I understand primary key, but i am
not sure what is the foreign key. thanks again.

"Allen Browne" wrote:

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's 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.


"JD McLeod" wrote in message
...
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.

.

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.

"Steve" wrote:

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



"JD McLeod" wrote in message
news
Thanks, Allen, I will give this a try. Can you explain what is meant by
"foreign key" in item d. of your post. I understand primary key, but i am
not sure what is the foreign key. thanks again.

"Allen Browne" wrote:

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's 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.


"JD McLeod" wrote in message
...
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.

.



.

Beautiful: what you've 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.


"JD McLeod" wrote in message
...
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.

"Steve" wrote:

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



"JD McLeod" wrote in message
news
Thanks, Allen, I will give this a try. Can you explain what is meant
by
"foreign key" in item d. of your post. I understand primary key, but i
am
not sure what is the foreign key. thanks again.

"Allen Browne" wrote:

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's 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.


"JD McLeod" wrote in message
...
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.

.



.