Grouping Question 1

[Blorf]

Hi.

I have a Union Query called FuelOD, it contains
VHID (Vehicle ID)
Date (Date)
Odometer


I need record for each unique VHID's, with the maximum date, and the masimum odometer for that date.

I just dunnow how to write the query. I could do it with vba, but that would be sloppy, and I know there is a way to do it.

Any advice would be appreciated.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[jebry]

Hi!

Select VHID, Max([Date]) As Latest Date, Max([Odometer]) As Largest Reading From FuelOD Group By VHID;

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

Something like this ?
SELECT A.VHID, A.Date, Max(A.Odometer) As MaxOdo
FROM FuelOD AS A INNER JOIN (
SELECT VHID, Max([Date]) AS MaxDate FROM FuelOD GROUP BY VHID
) AS B ON A.VHID = B.VHID AND A.Date = B.MaxDate
GROUP BY A.VHID, A.Date

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

 

[jebry]

Hi!

Good call PHV! I missed the maximum odometer reading 'for that date' in the original post. Your subquery is just the ticket!



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Blorf]

Hi Jebry. Your solution is what I tried first, but it gives max OD and Max date, even if the Max od takes place at an earlier date.

PHV, you rock. I see the solution works, but even though I stare at the code, I don't clearly understand whats happening.

We create set B with VHID and Max Date, join that to the same recordset, and get the max OD, but only for the matching B (Which already filters out all but max date)?

This is neat.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.