Junction Table with 3 tables - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Junction Table with 3 tables

Bruno Amaral

5 years ago

Hi everyone,

I'm struggling here to define the best design for my relationships. I'm creating a form for a purchase request (PR). A purchase request can have up to 3 suppliers. Currently I have the following tables:

tbSupplier = Contain supplier details
tbMaterial = Contain details of the items to be requested.
tbRequest = Contain generic information about the request, e.g. request number, request description, requester name...

tbRequestDetails = Contains the item codes and quantities, but here, I have three columns for each supplier, price and currency, like:
Supplier 1 | Price 1 | Currency 1 | Supplier 2 | Price 2 | Currency 2...

I believe there is a better way to organize the tbRequestDetails, but I couldn't find examples of many-to-many relationships with more than 2 tables.

	Scott Axton @Reply 5 years ago
Start out by watching the video here: Parts Inventory Tables See the Relationships video also. Richard covers Relational Database concepts in great detail in the Access Expert Level 1 AND Access Expert Level 2

Bruno Amaral OP @Reply

5 years ago

Hi Scott, thanks for your feedback, the example in the videos are always having a junction table that joins 2 tables.

In parts the junction table connects PartsT and SupplierT.
In relationships video the junction table connects DriverT and VehicleT

So, in both of this cases, the form takes a combination of 2 keys. My doubt is how to set a form where the following happens:
Materials are supplied by many suppliers
Requests can have many materials and the price of each material changes from request to request, even for same items from same suppliers.

I believe my junction table looks like:
tblRequestDetails:
RequestDetailsID (PK)
RequestID (FK)
MaterialID (FK)
SupplierID (FK)
Price
Currency

But I can't set a form for it

	Richard Rost @Reply 5 years ago
Multiple suppliers would definitely be a M2M relationship. See Event Enrollment for another good example of how to set this kind of a relationships up.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View