Forum Discussion
Solved
MS Access Many Junction Tables - Design Advice
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 ...
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.
Thanks for you reply. I've tried to create the relationships based in the concept above. Could you kindly check if is it correct?
looking my tbRequestDetails, it seems quite repetitive. Am I doing a wrong way to key in the information?
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.
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?
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:
I couldn't think how to transform my tbRequestDetails in this structure to the photo above, should I use a Query to organize?