Tables and design Many to Many ???

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

Duane Hookom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top