Grouping Query Help

  • Thread starter Thread starter scadav
  • Start date Start date
S

scadav

I am hoping some can help me with a query.

Lets say I have a table and the data looks as follows:

VehicleID(PK) LastUpdate GasTank
12 4/10/06 Full
12 4/20/06 Empty
12 5/20/06 Empty
13 4/23/06 Full
13 4/30/06 Full
13 5/01/06 Full
13 5/20/06 Full
14 5/20/06 Empty


For each vehicle, I am trying to find the last time some checked the
gas level. Once I find the date, I am trying to determine what the gas
level was at the time of the check.

So my query starts off like this:


SELECT Vans.VehicleID, Max(Vans.LastUpdate) AS MaxOfLastUpdate
FROM Vans
GROUP BY Vans.VehicleID


BUT if I add GasTank to this query, I have to add it to the "GROUPING",
which causes me to know have multiple groupings for each vehicle. I
tried using Last instead of Max, but in certain instances when a user
is adding entries, they don't necessarily do it in chronological order.

Anyone help would be appreciated.
 
scadav,

try the following query.

Select Vans.VehicleID, Vans.LastUpdate, Vans.GasTank
From Vans
Where (((Vans.LastUpdate) = (Select Max(V.LastUpdate) From Vans As V
Where V.VehicleID = Vans.VehicleID)));

HTH,
Brian
 
Wow. Worked like a charm. Thank you very much for your help.

Now I am just being curious...I understand how it worked worked except
for this part:
From Vans As V Where V.VehicleID = Vans.VehicleID)
Click to expand...

What is this for? How does this make it work?
 
Back
Top