Create a Query that looks up price based on date purchased, material code, po#, etc.

[ConstructionBoss]

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.

Factors:
1) 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
2) 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)
3) 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.

[June7]

This can be very tricky. Consider this method:

SELECT tblMaterial_Purchased.*, DLookUp("Current_Price","tblPrice_Updates","Kit_Nu mber=" & [Kit_Number] & " AND OP_Number=" & [OP_Number] & " AND [Effective_Date]=#" & DMax("Effective_Date","tblPrice_Updates","Kit_Numb er=" & [Kit_Number] & " AND OP_Number=" & [OP_Number] & " AND [Effective_Date]<#" & [Date] & "#") & "#") AS Price FROM tblMaterial_Purchased;