Calculate multiple values same field

[foshizzle]

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias, Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

 

[pbaldy]

How about:

http://allenbrowne.com/subquery-01.html#AnotherRecord

The alternative is adding the vehicle to the DLast() criteria, but that's a less efficient method (and probably the wrong function anyway).

 

[foshizzle]

Well, that looks almost exactly like what i need. but when I edit the sample to match my table and fields, im pulling nad data adter so many records for each vehicle. see the text doc attached.

SELECT tblOdometer.ID, tblOdometer.ODate, tblOdometer.Odometer, (SELECT TOP 1 Dupe.Odometer                 
   FROM tblOdometer AS Dupe                     
   WHERE Dupe.VehicleNum = tblOdometer.VehicleNum 
     AND Dupe.ODate < tblOdometer.ODate   
   ORDER BY Dupe.ODate DESC, Dupe.ID) AS PriorValue
FROM tblOdometer;

 

[stopher]

I think you need another step before creating the subquery. If you do the following query it will give you the keys to pull only the latest date for each vehicle which you can then re-join with you base table to get the latest reading. Use this with your current solution.

SELECT tblOdometer.VehicleNum, Max(tblOdometer.ODate) AS LatestDate
FROM tblOdometer
GROUP BY tblOdometer.VehicleNum;

I attach the solution also.

hth
Chris

 

[foshizzle]

Thanks. After after looking at it, Instead of just top 1, I am going to need the running difference. So i tried removing the Top 1 statement and just a select but its telling me "At most one record can be returned by this subquery"