Many to Many Relationship

[thanie]

Hi, I have two tables, a CAD Drawing table, and a Parts
table. I have made a many-to-many rel. with a BOM (bill
of materials) table as the junction table. One drawing
can have many parts, and one part can go on many drawings.
So, right now, I have a table listing all my drawings,
table listing all my parts, and the BOM table just has the
field headings, but no information from either tables. My
question is, how do I make the connection between certain
drawings and certain parts? Do I need to add the primary
key from each table to the other's table and then do a
query? Or is there an easier way, because if I have a
gasket for example, it could go on hundreds of drawings,
which would mean 1 field for every drawing? Anyone please
help, as I'm banging my head on my desk as we speak.

Thanks!

 

[MDW]

At the very least, your junction table should contain the primary key from each of the two constituent tables, which would comprise a composite primary key. Depending on your specific situation, you may also want to add a Quantity field to the junction table (drawing A might need 3 gaskets, for instance)

Assume the tables look like this

DRAWING
DRAWING_ID DESCRIPTIO
D001 Bicycle gear
D002 Jet propulsion engin

BO
PART_ID DESCRIPTIO
P1 Gear toot
P2 Big round part of the gea
P3 Jet turbin

JUNCTIO
DRAWING_ID PART_ID QT
D001 P1 200
D001 P2
D002 P1 300
D002 P2
D002 P3

What this would say is that drwing D001 ("bicycle gears") requires 2000 gear teeth and 2 big round gear wheels. Drawing D002 ("jet turbine") would requite 3000 gear teeth, 3 gear wheels, and 1 jet turbine

In order to get your final results set, you would need to include all three tables

----- thanie wrote: ----

Hi, I have two tables, a CAD Drawing table, and a Parts
table. I have made a many-to-many rel. with a BOM (bill
of materials) table as the junction table. One drawing
can have many parts, and one part can go on many drawings.
So, right now, I have a table listing all my drawings,
table listing all my parts, and the BOM table just has the
field headings, but no information from either tables. My
question is, how do I make the connection between certain
drawings and certain parts? Do I need to add the primary
key from each table to the other's table and then do a
query? Or is there an easier way, because if I have a
gasket for example, it could go on hundreds of drawings,
which would mean 1 field for every drawing? Anyone please
help, as I'm banging my head on my desk as we speak

Thanks

 

[DDM]

Thanie, the junction table in a many-to-many relationship (in this case your
BOM table) typically contains, as foreign keys, the primary key fields from
the tables it joins. This means that your junction table is in a one-to-many
relationship (on the "many" side) with each of the two tables it joins. Each
record in your junction table will represent the coming-together of a
specific drawing and a specific part. So if, to take your example, a gasket
appears on hundreds of drawings, you will have that many hundreds of
records, each one bringing together the ID for the gasket with the ID from
one of the drawings it appears on. Conversely, if a drawing has, say, four
parts, you will create four records in the junction table, each containing
the drawing ID and the ID of one of the four parts. That is how you make the
connection between drawings and parts.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[MDW]

In my reply, I referred to your "Parts" table as "BOM". The table marked as "JUNCTION" would be the BOM table. Sorry.

 

[Tim Ferguson]

My
question is, how do I make the connection between certain
drawings and certain parts? Do I need to add the primary
key from each table to the other's table
Yes.

Or is there an easier way, because if I have a
gasket for example, it could go on hundreds of drawings,
which would mean 1 field for every drawing?

No: it's one BOM record for each drawing of each part. The original design
is right, but you next need to think about your user interface. You could:

Have a form designed on the Parts table, and a multi-select list box
RowSourced on the Drawings table. You could check off the relevant
drawings, and then the form would go through the Selected() array and write
a row for each drawing to the BOM table.

Or vice versa.

This one is hard in Access, but easier in VB proper: use Drag-and-drop to
pick up a part and drop it on a drawing.

For A-level success, get your scanner and OCR software to scan the
drawings, identify the parts, and write the rows to the BOM table.

Either way, if you have 100 drawings with 50 parts on each, then you need
to write 5000 records to the BOM table.

Hope that helps


Tim F