Join Type - Populate Table from Multiple Fields

[Karl Burrows]

I may be approaching this from the wrong direction, but I have a small
database that has basic contact information. There is another table that is
used to populate a field called "Committees". If I open the committees
table, I can see the grouping of all members for each committee name. That
works fine. Now I am told, there could be people on multiple committees.
So, now I need to figure out how to populate the form (I created 3
additional fields called Committee2, 3, etc.) as well as populating the
committee table to show a member in multiple committees. I tried a
self-join to see if that would work, but it only shows the first committee
field. I need it grouped so I can create reports by committee as well.

Any thoughts? Thanks!!

 

[Tom Wickerath]

Hi Karl,

You don't want additional fields for storing new committees. What you really need is to create a
many-to-many (M:N) relationship, using two one-to-many (1:M) relationships joined with a linking
table. This is the method used to model M:N relationships. The linking table will include a
minimum of two fields, which store the corresponding primary key values from the base tables in
what is known as foreign key fields. This design allows you to add additional members or
committees without requiring design changes to the underlying tables. Something to remember when
designing a database: "Fields are expensive, records are cheap". In other words, once you start
creating queries, forms and reports based on your design, you don't want to be forced into a
situation of having to add fields to accommodate new data.

You can display the data using a main form with a subform. The main form could include a record
to display each member, with the subform displaying the committees that member has joined.
Alternatively, the main form can display each committee, with the subform displaying the members
of that committee.

Here are some links to some database design documents that I think you will find helpful:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

Also recommended: Find the copy of Northwind.mdb that is probably already installed on
your hard drive. Study the relationships between the various tables (Tools >
Relationships...)

I will send some additional information directly to you.

Tom
_____________________________________________


I may be approaching this from the wrong direction, but I have a small
database that has basic contact information. There is another table that is
used to populate a field called "Committees". If I open the committees
table, I can see the grouping of all members for each committee name. That
works fine. Now I am told, there could be people on multiple committees.
So, now I need to figure out how to populate the form (I created 3
additional fields called Committee2, 3, etc.) as well as populating the
committee table to show a member in multiple committees. I tried a
self-join to see if that would work, but it only shows the first committee
field. I need it grouped so I can create reports by committee as well.

Any thoughts? Thanks!!