I would like to have a Query in my database that returns what the price should be for materials we purchase. The price is dependent on multiple factors.
- Prices adjust on various days for various materials at various locations. I need to match the material purchased to the price in effect after the date in tblPrice_Updates
- The prices are linked to an OP number (a Purchase Order, our shorthand nomenclature is backwards!) and a Plant Number I use this OP number in tblMaterial_Purchased in a combo select box so the person entering data can pick the correct OP based on the other values (Vendor Name and Plant Number)
- In the tblMaterial_Purchased they enter the Kit_Number (Material Code) to determine what we bought. This field is also in tblPrice_Updates.
The idea is that after putting in the date, Kit_Number, Plant_Number (Probably irrelevant for the Query since the OP_Number is tied to specific plants), and the OP_Number in tblMaterial_Purchased, the new query would return a price for each line based on the date bought.
Attached is a sable database that had been sanitized.