MS Access: Selecting Records from 2 Tables Without Foreign Key

Question

I'm tracking my driving habits in MS Access 2016. I have a table called Miles:

In my Miles table, I'm recording information from my car's dash at the end of each drive.

I also have a 2nd table (actually a query) called Fuel:

My Fuel query shows when I purchased fuel and for how much.

I want to create a query that shows that shows the greatest Transaction_Date that is less than or equal to each Miles_Date. My expected output would look something like this:

I tried the following Select statement:

SELECT 
    Miles.Miles_ID, 
    DMax("[Transaction_Date]", "Fuel", "[Fuel]![Transaction_Date] <= [Miles]![Miles_Date]") AS Fuel_Date, 
    Miles.Miles_Date, Miles.Miles, Miles.MPG
FROM 
    Miles;

I get the error:

Microsoft Access cannot find the name [Miles]![Miles_Date]

Answer 1

When using a domain aggregate, you need to use string concatenation to pass values from the current row, like this:

SELECT 
    Miles.Miles_ID, 
    DMax("[Transaction_Date]", "Fuel", "[Fuel].[Transaction_Date] <= #" & Format(Miles.Miles_Date, "yyyy-mm-dd") & "#") AS Fuel_Date, 
    Miles.Miles_Date, Miles.Miles, Miles.MPG
FROM 
    Miles;

However, using a domain aggregate in a query is a bad practice, since it limits the influence of the optimizer. When possible, use a subquery instead:

SELECT 
    Miles.Miles_ID, 
    (SELECT Max([Transaction_Date]) FROM Fuel WHERE [Fuel].[Transaction_Date] <= Miles.Miles_Date) AS Fuel_Date, 
    Miles.Miles_Date, Miles.Miles, Miles.MPG
FROM 
    Miles;

This will both run faster, and not rely on string concatenation.