SOLVED

MS Access Many Junction Tables - Design Advice

%3CLINGO-SUB%20id%3D%22lingo-sub-2495317%22%20slang%3D%22en-US%22%3EMS%20Access%20Many%20Junction%20Tables%20-%20Design%20Advice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2495317%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20hope%20everything%20is%20safe%20at%20your%20side.%20I'm%20trying%20to%20figure%20out%20in%20a%20model%20of%20relationship%20that%20could%20work%20for%20my%20case.%20Here%20is%20the%20situation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20%3CSTRONG%3EtbPurchaseRequest%26nbsp%3B%3C%2FSTRONG%3E%20that%20will%20have%20a%20%3CSTRONG%3EtbPurchaseRequestDetails%3C%2FSTRONG%3E%20with%20the%20items'%20list%20(coming%20from%20%3CSTRONG%3EtbMaterials%3C%2FSTRONG%3E).%20In%20one%20purchase%20request%2C%20I%20can%20have%20up%20to%203%20Suppliers%20(stored%20in%20%3CSTRONG%3EtbSupplier%3C%2FSTRONG%3E).%20The%20prices%20changes%20per%20supplier%20for%20each%20request%2C%20so%2C%20I%20need%20to%20capture%20the%20price%20for%20that%20specific%20request.%20Here%20are%20my%20doubts%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Where%20is%20the%20best%20table%20to%20capture%20the%20quantity%20requested%20and%20the%20price%20information%3F%3C%2FP%3E%3CP%3E2.%20What%20is%20the%20most%20logical%20way%20to%20organize%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2495317%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2496603%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20Many%20Junction%20Tables%20-%20Design%20Advice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2496603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090526%22%20target%3D%22_blank%22%3E%40_brunoga%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20general%20rule%2C%20I%20avoid%20any%20suggestion%20that%20one%20solution%20is%20%22the%20best%22.%20All%20too%20often%20that%20depends%20on%20a%20number%20of%20factors%2C%20and%20any%20solution%20has%20to%20be%20evaluated%20in%20context.%20What%20is%20%22best'%20in%20one%20situation%20may%20or%20may%20not%20be%20the%20%22best%22%20in%20a%20different%20situation.%20So%20let's%20talk%20about%20the%20standard%2C%20ore%20most%20often%20recommended%20approaches%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuantity%20requested%20is%20a%20fact%20about%20one%20specific%20request.%20Therefore%2C%20it%20belongs%20in%20the%26nbsp%3B%3CSTRONG%3EtbPurchaseRequestDetails%20table%26nbsp%3B%20%3C%2FSTRONG%3Eas%20part%20of%20each%20line%20item.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrice%20is%20more%20complicated%20(see%20above%20comments%20on%20%22best%22%20solutions).%20If%20you%20need%20to%20capture%20%22Price%20BY%20Supplier%20AS%20OF%20purchase%22%2C%20and%20you%20want%20that%20to%20be%20part%20of%20the%20record%20for%20a%20purchase%2C%20then%20it%20too%20is%20a%20fact%20about%20one%20specific%20request%2C%20and%20it%20too%20belongs%20in%20the%20same%26nbsp%3B%3CSTRONG%3EtbPurchaseRequestDetails%20%3C%2FSTRONG%3Etable.%20IMO%20that%20is.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20other%20ways%20to%20capture%20the%20%22Price%20as%20of%22%20value%2C%20including%20a%20more%20complicated%20query%20that%20looks%20at%20the%20date%20of%20a%20purchase%20and%20at%20the%20%22effective%20date%22%20of%20a%20price%20for%20a%20material%20for%20a%20supplier.%20That%20might%20be%20considered%20a%20more%20standard%20approach%20from%20the%20perspective%20of%20Normalization.%20It%20would%20mean%20that%20you%20don't%20store%20the%20price%20in%20two%20places.%20However%2C%20in%20many%20cases%2C%20it's%20a%20lot%20easier%20to%20sacrifice%20a%20bit%20of%20purity%20in%20Normalization%20and%20have%20the%20%22Price%20BY%20Supplier%20AS%20OF%20purchase%22%20value%20stored%20with%20that%20purchase%20for%20historical%20purposes.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20it%20depends%2C%20in%20part%2C%20on%20your%20situation%20and%20your%20requirements.%20That's%20how%20I%20would%20probably%20do%20it%2C%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2497732%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20Many%20Junction%20Tables%20-%20Design%20Advice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2497732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20you%20reply.%20I've%20tried%20to%20create%20the%20relationships%20based%20in%20the%20concept%20above.%20Could%20you%20kindly%20check%20if%20is%20it%20correct%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_brunoga_0-1624981674995.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292333i9936A227E55FFBF5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_brunoga_0-1624981674995.png%22%20alt%3D%22_brunoga_0-1624981674995.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elooking%20my%20%3CSTRONG%3EtbRequestDetails%3C%2FSTRONG%3E%2C%20it%20seems%20quite%20repetitive.%20Am%20I%20doing%20a%20wrong%20way%20to%20key%20in%20the%20information%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_brunoga_1-1624981797901.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292334iF91FDB18FFBE844C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_brunoga_1-1624981797901.png%22%20alt%3D%22_brunoga_1-1624981797901.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2497895%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20Many%20Junction%20Tables%20-%20Design%20Advice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2497895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090526%22%20target%3D%22_blank%22%3E%40_brunoga%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20think%20there%20is%20still%20some%20modification%20needed%2C%20assuming%20I%20understand%20properly%20what%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20the%20assumption%20is%20that%20you%20want%20the%20%22AsOfRequestDate%22%20Price%20to%20be%20associated%20with%20the%20material%20purchased%2C%20and%20that%20Price%20is%20specific%20to%20one%20particular%20Supplier%20for%20that%20particular%20Material%20in%20effect%20as%20of%20that%20date.%20Note%20that%20you%20must%20store%20the%20Price%20somewhere%20else%20and%20that%20isn't%20shown%20in%20this%20screenshot.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETherefore%2C%20if%20I%20were%20to%20do%20this%2C%20I%20would%20have%20another%20table%20that%20joins%20suppliers%20and%20materials.%20It%20is%20also%20a%20junction%20table%20with%20FIVE%20fields.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESupplierMaterialID%20(AutoNumber%20PK)%3C%2FP%3E%3CP%3ESupplierID%20FK%20from%20Supplier%3C%2FP%3E%3CP%3EMaterialID%20FK%20from%20Material%3C%2FP%3E%3CP%3ECurrentPrice%20This%20supplier's%20price%20for%20this%20material%3C%2FP%3E%3CP%3EAsOfPriceDate%20The%20date%20this%20price%20is%20effective%20for%20this%20material%20for%20this%20supplier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20joining%20both%20materials%20and%20suppliers%20to%20the%20tblRequestDetails%20table%2C%20you%20would%20only%20need%20the%20SupplierMaterialID%20(which%20identifies%20the%20two%20other%20values%2C%20MaterialID%20and%20SupplierID)%20and%20the%20CurrectPrice%2C%20determined%20as%20the%20price%20effective%20on%20the%20most%20recent%20AsOfPriceDate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone, I hope everything is safe at your side. I'm trying to figure out in a model of relationship that could work for my case. Here is the situation:

 

I have a tbPurchaseRequest  that will have a tbPurchaseRequestDetails with the items' list (coming from tbMaterials). In one purchase request, I can have up to 3 Suppliers (stored in tbSupplier). The prices changes per supplier for each request, so, I need to capture the price for that specific request. Here are my doubts:

 

1. Where is the best table to capture the quantity requested and the price information?

2. What is the most logical way to organize this?

 

Any advice?

9 Replies

@_brunoga 

As a general rule, I avoid any suggestion that one solution is "the best". All too often that depends on a number of factors, and any solution has to be evaluated in context. What is "best' in one situation may or may not be the "best" in a different situation. So let's talk about the standard, ore most often recommended approaches,

 

Quantity requested is a fact about one specific request. Therefore, it belongs in the tbPurchaseRequestDetails table  as part of each line item.

 

Price is more complicated (see above comments on "best" solutions). If you need to capture "Price BY Supplier AS OF purchase", and you want that to be part of the record for a purchase, then it too is a fact about one specific request, and it too belongs in the same tbPurchaseRequestDetails table. IMO that is. 

 

There are other ways to capture the "Price as of" value, including a more complicated query that looks at the date of a purchase and at the "effective date" of a price for a material for a supplier. That might be considered a more standard approach from the perspective of Normalization. It would mean that you don't store the price in two places. However, in many cases, it's a lot easier to sacrifice a bit of purity in Normalization and have the "Price BY Supplier AS OF purchase" value stored with that purchase for historical purposes. 

 

Again, it depends, in part, on your situation and your requirements. That's how I would probably do it, though.

@George Hepworth 

 

Thanks for you reply. I've tried to create the relationships based in the concept above. Could you kindly check if is it correct?

 

_brunoga_0-1624981674995.png

 

looking my tbRequestDetails, it seems quite repetitive. Am I doing a wrong way to key in the information?

_brunoga_1-1624981797901.png

 

@_brunoga 

 I think there is still some modification needed, assuming I understand properly what you need.

 

Again, the assumption is that you want the "AsOfRequestDate" Price to be associated with the material purchased, and that Price is specific to one particular Supplier for that particular Material in effect as of that date. Note that you must store the Price somewhere else and that isn't shown in this screenshot. 

 

Therefore, if I were to do this, I would have another table that joins suppliers and materials. It is also a junction table with FIVE fields.

 

SupplierMaterialID (AutoNumber PK)

SupplierID FK from Supplier

MaterialID FK from Material

CurrentPrice This supplier's price for this material

AsOfPriceDate The date this price is effective for this material for this supplier.

 

Instead of joining both materials and suppliers to the tblRequestDetails table, you would only need the SupplierMaterialID (which identifies the two other values, MaterialID and SupplierID) and the CurrectPrice, determined as the price effective on the most recent AsOfPriceDate.

 

Hi @George Hepworth thanks for your quick reply, I'm in Asia, so our time zones are completely opposite haha

 

When I meant save the price as per that date it is like:

 

Request 1 on 01/01/21

Banana Price from supplier A is USD 1.00

 

Request 2 on 06/06/21

Banana Price from supplier A is USD 1.10

 

Technically, I don't need to have a field AsOfPriceDate, do I? I my mind I could use the RequestDate from tbRequest.

 

This is my current template (done in excel), we have 1 row per item, first two columns the user will type the ItemType and Quantity and then is repeated 3 times the Price, Currency and Delivery.

_brunoga_0-1625042221499.png

 

Basically I was trying to replace this template with access.

 

So, an user will create a Purchase Request using 1, 2 or 3 quotation (depends of the total value). And then I need to store the information of:

 

1. RequestID

2. RequestDate

3. RequesterName 

4. temCode

5. ItemQnt

6. ItemDeliveryDate

 

These 6 fields are common to all suppliers.

 

Then, once we include a supplier, user will need to fill the price for each item for that supplier and the currency. So, the price is liked to a date through the RequestID. Does it make sense?

 

I think that makes more sense, yes. I am not sure whether the user needs to fill in a price each time they add a request. Do prices change too often to be stored in the table with suppliers' materials?

@George Hepworth 

 

Prices will change every 3 to 6 months normally.

If I stick to the previous relationship structure that I shared, is it possible to organize the form having the supplier selection in the header and then all the other details as a continuous form?

 

Header carries all information from tbRequest and then user fill in ItemDescription (combobox) and fill prices and currency for that item (will probably add a checkbox like "use same currency for all items under the same supplier")

 

Something like:

_brunoga_1-1625066835720.png

I couldn't think how to transform my tbRequestDetails in this structure to the photo above, should I use a Query to organize?

 

best response confirmed by _brunoga (Occasional Contributor)
Solution

@_brunoga 

 

You are right. A properly normalized table design doesn't lend itself to that multicolumn interface layout. You could use a crosstab query to achieve the layout, but then it would not be editable. Another option is to go to an unbound form based on that layout, and use VBA to write the values back into the various tables. 

However, the most familiar way to accomplish it is one that is illustrated in this demo on my website.

@George Hepworth 

Gorge, I've seen your DB, your videos and I still couldn't understand how to do it.

In that example of artists and songs it is a junction table with 2 tables involved and 2 keys (ArtistID and SongID). In this case I'm clear with it and the subform.

In my case I will have 3 tables involved. Do you have an example with 3 tables?

@_brunoga 

You'd manage that with TWO combo boxes on the subform instead of just one. The two combo boxes are bound to the second and third tables involved in the junction table.