Create a Query that looks up price based on date purchased, material code, po#, etc.
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.