Creating Database Representing Two Chart of Accounts

I am creating an Access 2007 Database that will need to model a global chart
of accounts for a multinational firm I am currently contracting for. There
are two types of charts... one is a group chart that has accounts that are
only 6 digits long (e.g. 600000) and the second is a more detailed operating
chart with 10 digit accounts (e.g. 6000001111). All accounts in the operating
chart are required to contain a field representing the 6 digit group account
that it rolls up to (e.g. 6000001111 would contain a group account field with
the value 600000). Other than the requirement of a Group account mapping to
Operating account both tables would be exactly the same. Is this something
that should be modeled with 2 separate tables or should all the account data
be within one table with special validation rules in place to distinguish
between Operating and Group accounts?

So, for any account, you need to handle 2 things:
- the group it belongs to (required)
- the operation it belongs to (may not apply.)

This would suggest 2 fields in your table:
- AccountGroup is required, and would be a foreign key to a table of account
groups.

- AccountOperation is nullable. (You can still make it a foreign key with
referential integrity.)

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

"Chart Builder" wrote in message
...
I am creating an Access 2007 Database that will need to model a global
chart
of accounts for a multinational firm I am currently contracting for. There
are two types of charts... one is a group chart that has accounts that are
only 6 digits long (e.g. 600000) and the second is a more detailed
operating
chart with 10 digit accounts (e.g. 6000001111). All accounts in the
operating
chart are required to contain a field representing the 6 digit group
account
that it rolls up to (e.g. 6000001111 would contain a group account field
with
the value 600000). Other than the requirement of a Group account mapping
to
Operating account both tables would be exactly the same. Is this something
that should be modeled with 2 separate tables or should all the account
data
be within one table with special validation rules in place to distinguish
between Operating and Group accounts?