-1

Please excuse my formatting... I am working on that.

I am trying to select the correct price of a project in my query based off the appropriate effective date.

For example we have a table that has the following information

Price Table-------------------------

Item Price effectiveDate

  • A $0.57 1/1/17
  • A $0.72 6/1/17

Now I have a production table that contains what was produced that day and it will list out their quantity and the production for a production date.

Production Table-------------------

Item Quantity productionDate

  • A 100 2/1/17
  • A 100 7/1/17

Now when I query these I want to be able to select the appropriate price given the productionDate and effectiveDate.

What is the best way to achieve this?

James
  • 1
  • 1

1 Answers1

2

Try the following (first select the highest effectiveDate lower than the productionDate, then get the price for that date):

SELECT preselection.Item, Price FROM  
(SELECT Production.Item, Max(effectiveDate) As MaxEffectiveDate
FROM Production INNER JOIN Price ON Price.item = Production.Item
Where Price.effectiveDate <= productionDate GROUP BY Production.Item) As preselection 
INNER JOIN Price ON Price.Item = preselection.Item 
AND Price.effectiveDate = preselection.MaxEffectiveDate
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 1
    @James In case you are very new to queries and SQL, the inner query (in parenthesis) can be saved as a separate query, for example as [Get Production Effective Date]. Then that saved query can be referenced in the other query like `SELECT preselection.Item, Price FROM [Get Production Effective Date] As preselection INNER JOIN Price ON Price.Item = preselection.Item AND Price.effectiveDate = preselection.MaxEffectiveDate`. The benefit to this approach is that both queries can be separately edited and tested using the Access Design View. – C Perkins Aug 15 '17 at 15:37