Jun 28 2021 10:09 PM
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?
Jun 29 2021 04:36 AM
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.
Jun 29 2021 08:50 AM
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?
Jun 29 2021 09:22 AM
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.
Jun 30 2021 05:14 AM
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?
Jun 30 2021 06:20 AM
Jun 30 2021 08:29 AM - edited Jun 30 2021 08:29 AM
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?
Jun 30 2021 08:51 AM
Solution
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.
Jul 01 2021 09:25 AM
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?
Jul 01 2021 09:43 AM
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.
Jun 30 2021 08:51 AM
Solution
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.