Find Last Date for a criteria in Query

[Retroboro]

Hi (first post),

I have 2 tables, one showing sales and the other showing the exchange rate. I want to be able to identify the exchange rate when the sale was made.



However the exchange rate table only doesn't have a record for every date:

Table 1 (Sales)

Date, Sales Value
01/11/2018, £10
02/11/2018, £20
03/11/2018, £10
04/11/2018, £30

Table 2 (Exchange Rate)
Date, Ex Rate
01/11/2018, 1.14
03/11/2018, 1.16
04/11/2018, 1.17

The result I'm looking for is:

Date, Sales Value, Ex Rate
01/11/2018, £10, 1.14
02/11/2018, £20, 1.14
03/11/2018, £10, 1.16
04/11/2018, £30, 1.17

Any help would be much appreciated. Thanks

[Minty]

Put the exchange table rate in your query and in the criteria put <= [SalesTable].[SalesDate]

Please rename the Date fields. Date is a function and reserved word in Access.

[Retroboro]

Hi thanks, but I'm struggling to get it to work. I've simplified the field names, hence "Date". but when I run the SQL

SELECT DISTINCT Sales Data.Date, Sales Data.[Sales Value], Exchange Rate.Ex Rate
FROM Sales Data LEFT JOIN Exchange Rate ON Sales Data.Date = Exchange Rate. Ex Date
WHERE (((Exchange Rate.Ex Rate)<=[Sales Data]![Date]));

the result I get is:

01/11/2018, £10, 1.14
03/11/2018, £10, 1.16
04/11/2018, £30, 1.17

[June7]

Should also avoid spaces in field names. If used must enclose in [ ]. If you use the query designer, Access should provide the [ ]. I am surprised you get anything with the posted structure.

One solution involves domain aggregate function:

SELECT Sales.Date, Sales.[Sales Value], DMax("[Ex Rate]","Exchange Rate","[Date]<=#" & [Date] & "#") AS ER FROM Sales;

Note the lack of [ ] around the table name. This is a weird exception to the requirement for [ ].

Assumes there is only 1 rate for each date.