many to many relationship

[Guest]

I have a table for my drawings(DWG) and a table for the customer parts that
can be made from my drawings (PRT). Each DWG must have at least one PRT
associated with it but can have many PRTs. Each PRT does not have to be
associated with a DWG, but one PRT can be associated with many DWGs.

My question is, how would I store the relations in this situation? The only
way I see is to have a comma-diliminated tex/memo field, but that would not
allow an easy query.

 

[Guest]

To create a many to many relationship you need to create a junction table
that goes in between your two tables. You might call this table trelDWG.
The junction table has just three fields. The first field you call RowID,
which represents instances where individual values in your two tables meet.
The second and third fields are the foreign keys to each of your two tables.

 

[Guest]

One solution would be to use three tables:

TblDrawings
DrawingsID (key)
DrawingName

TblParts
PartsID (key)
PartName

TblDrawingParts
DrawingPartsID (key)
DrawingsID
PartsID

In your relationships chart, relate the PartsID's and the DrawingsID's.
Create a Form with subforms. The main form will have the Drawing Name and
ID, and the subform will have all of the parts associated with that DrawingID.

Hope this makes sense.

 

[Guest]

Thank you, this was very helpful.

One solution would be to use three tables:

TblDrawings
DrawingsID (key)
DrawingName

TblParts
PartsID (key)
PartName

TblDrawingParts
DrawingPartsID (key)
DrawingsID
PartsID

In your relationships chart, relate the PartsID's and the DrawingsID's.
Create a Form with subforms. The main form will have the Drawing Name and
ID, and the subform will have all of the parts associated with that DrawingID.

Hope this makes sense.