Many To Many To Many

[Nanette]

I have a Parts table, a Purchase Orders table, and a Purchase Requisitions
Table.
These are all many to many relationships and we will need to pull data from
all tables, such as PR's and Parts data, PR's and PO's data, PO's and Parts.

Is it possible to have one Linking Table that links three tables?

OR

Can I make a Linking Table between each table and have them all connected?
Kind of like a circular design?

OR

Should I just put linking tables between the Parts and Purchase Orders table
and another between the Parts and Purchase Requisition tables? But then how
do I get the connection between the Purchase Orders and Purchase Requisition
tables?

I'm going in circles, can anyone help!

 

[Jeff L]

I don't think you need the linking tables at all. The parts table
layout, I'm assuming, is something like PartID, PartDescription. Then
you use the Purchase Orders to purchase Parts. This would be a
one-to-many relation. Each Purchase Order would refer to one PartID.
I don't know what the difference is between a PO and a PR. If I missed
something, let me know and maybe I can assist you further.

 

[Nanette]

Hi Jeff,

You are right about the Parts Table having a PartID and PartDescriptin.

A purchase requisition is a request to purchase goods and create a purchase
order. One step before getting a purchase order.

The original purchase requisition has many part numbers on it to order. When
the purchase requsition is okayed, then there may be two or more purchase
orders to order the parts (multiple vendors). Plus, some automated system is
creating a purchase requisitions for each part, so several purchase
requisitions are being assigned to one purchase order so the order can go to
one vendor.

A very strange system. What do you think?

 

[Jeff Boyce]

Nanette

If your situation calls for a way to resolve many parts and many POs and
many PRs, yes, you can create a junction/resolver/relation table that pulls
in the IDs from all three of these tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nanette]

Thanks Jeff,

That will make my life much easier!