Jolien .A Jolien .A - 1 year ago 67
SQL Question

Access SQL: Select most recent date rather than list of dates

I have a table of commodity prices and price dates and another table with supplier transaction data that also has a column with dates. The price dates and supplier dates column are not identical. If I pick a date from the supplier table, I want to retrieve the commodity price of the date closest to that supplier transaction date. The commodity price date can be before or equal to the supplier transaction date but not later than this date.

[List3 is the possible supplier dates and List4 is the commodity]

SELECT TOP 1 CommPrices.Price
FROM CommPrices
WHERE (((CommPrices.DateComm)<=[Forms]![Tool Should Cost]![List3]) AND ((CommPrices.Commodity)=[Forms]![Tool Should Cost]![List4]))
ORDER BY CommPrices.DateComm DESC;

Example of what I want:

Assume there is the commodity copper with the following date and price data:
01/01/2015 $10.00 & 01/01/2016 $5.00 & 06/06/2016 $15.00

If I select a random supplier date at lets say february first 2016, then the most recent date is 01/01/2016 and thus I should retrieve $5.

Answer Source

Just add TOP 1 and ORDER BY:

SELECT TOP 1 CommPrices.Price
FROM CommPrices
WHERE CommPrices.DateComm <= [Forms]![Tool Should Cost]![List 3]
ORDER BY CommPrices.DateComm DESC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download