Query to return Data based on Max Date Entry 2

[OzzieTheOwl]

Hi

I have a table with 3 fields
Registration, Date, Miles

This shows a history of vehicle mileage at the date stated. So there are multiple entries for each vehicle, with a different date against each with a mileage at that date.

What I need to do is write a query to produce one line per Vehicle, showing Registration, Max Date, and mileage at the Max Date.

Is this possible? If so could someone point me in the right direction to do this.

Thanks in Advance

Barney

 

[PHV]

One way (SQL code):
SELECT A.Registration, A.Date, A.Miles
FROM yourTable AS A INNER JOIN (
SELECT Registration, Max([Date]) AS LastDate FROM yourTable GROUP BY Registration
) AS L ON A.Registration = L.Registration AND A.Date = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OzzieTheOwl]

Hi

PHV - When I put that SQL Code in, I get the following error

Syntax error in From Clause
And the second SELECT is highlighted.
I had to modify the code to account for different field names, so the SQL I tried is below.

SELECT A.Reg,A.Txn_Dt,A.Mile
FROM tblFuelArchive AS A INNER JOIN (
SELECT Reg, Max([Txn_Dt]) AS LastDate FROM tblFuelArchive GROUP BY Reg
) AS L ON A.Reg = L.Reg AND A.Txn_Dt = L.LastDate

Have I done something obvoiusly wrong?

Cheers

Barney

 

[PHV]

Which version of access ? Should be 2000 or above.
If still with 97, create a query named, say, qryLastDate:
SELECT Reg, Max(Txn_Dt) AS LastDate
FROM tblFuelArchive
GROUP BY Reg

And now your query:
SELECT A.Reg,A.Txn_Dt,A.Mile
FROM tblFuelArchive AS A INNER JOIN qryLastDate AS L ON A.Reg = L.Reg AND A.Txn_Dt = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OzzieTheOwl]

PHV

Thank you.
I am using Access 97, sorry I should have stated that at the start.

What you suggested has worked perfectly.
Have a Star on me.

Thanks

Barney