Tables and design Many to Many ???

  • Thread starter Thread starter DB
  • Start date Start date
D

DB

I have a DB to inventory christmas ornaments. A table contains name and specs
of the ornaments. Another table list members of the family who own the
ornaments. A one to many relation is established as one person owns many
ornaments. This works fine.

I need to relate the members and the ornaments to reflect that some
ornaments are owned by more than one person. I tried a many to amny relation
but can't get it to work with forms/reports. Should I continue to pursue a
many to many relationship or is there another way.

The reports / forms need to be able to list individual ornaments with specs
and all owners. The primary form should list owners as the records and a
subform will list all ornaments owned by that person. Ornaments that are
co-owned by many members simply show up in the list of ornaments for each of
those members.
 
You need at least three tables:
tblFamilyMembers
============
FamMemID autonumber primay key
--- etc ----

tblOrnaments
============
OrnaID autonumber primary key
--- etc ----

tblFamMemOrnaments (Junction table)
==============
FamMemID foreign key to tblFamilyMembers.FamMemID
OrnaID foreign key to tblOrnaments.OrnaID

Main forms will be based on either of the first two tables while the
subform(s) will be based on the junction table
 
Back
Top