Table Design

[Suzanne]

I have a table called Companies, which has a different company id for each
company. I also have a table called Accountant CC, which has a different
accountant id for each accountant. In my Companies table I have a combo box
which lists the Accountant Id (pulled from my Accountant CC table). We have
a couple of problems with this, I can only list one accountant id at a time,
and sometimes there are more than one accountant cc for a company, also I
want to be able to do a mail merge of the accountant cc's based on criteria
from my companies table, like say, list all accounts who are cc’s of
companies in NJ. I’m having a problem connecting them because a lot of the
time, my companies have more than one accountant cc, and vise versa, which
would mean I would need to use more than one accountant id in my acct id
field in my companies table. Was wondering if anyone had any suggestions on
what to do to resolve this issue, hope all of that made sense. Oh and I use
MS Access 2003.

 

[John W. Vinson]

I have a table called Companies, which has a different company id for each
company. I also have a table called Accountant CC, which has a different
accountant id for each accountant. In my Companies table I have a combo box
which lists the Accountant Id (pulled from my Accountant CC table). We have
a couple of problems with this, I can only list one accountant id at a time,
and sometimes there are more than one accountant cc for a company, also I
want to be able to do a mail merge of the accountant cc's based on criteria
from my companies table, like say, list all accounts who are cc’s of
companies in NJ. I’m having a problem connecting them because a lot of the
time, my companies have more than one accountant cc, and vise versa, which
would mean I would need to use more than one accountant id in my acct id
field in my companies table. Was wondering if anyone had any suggestions on
what to do to resolve this issue, hope all of that made sense. Oh and I use
MS Access 2003.

This is a classic "Many to Many" relationship: each Company can have zero,
one, or many accountants, and each accountant may work for zero, one or many
companies.

The solution is to not put ANY accountant information in the Companies table,
nor any Company information in the Accountant CC table. Instead, add *a new
table*, CompanyAccountants let's say. This would have a field for the
CompanyID and one for the AccountantID (and perhaps some other fields if you
need to distinguish the roles of the different accountants). If XYZ Corp has
three accountants cooking... erm... managing their books, there would be three
records in this table. A Subform based on this table could be put on either
the Accountant or Companies data entry forms to select the appropriate company
or accountant.