Running total multiple record values

[foshizzle]

Hi,

I am having an issue with my running total query.
It consists of a running total per vehiclenum. All data comes from one table.

It works properly only on the first vehiclenum of the query. After that, the first "previous" odometer reading of each subsequent vehiclenum starts at some erroneous number, throwing the remainder of each vehiclenum running total.

Here is the code for the query,

SELECT qry_ODO_TotalSub.ID AS OdomAlias, qry_ODO_TotalSub.ODate, qry_ODO_TotalSub.VehicleNum, qry_ODO_TotalSub.Odometer, Nz(DLast("Odometer","qry_ODO_TotalSub","[ID] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference, Nz(DFirst("Odometer","qry_ODO_TotalSub"),0) AS StartOD, [Odometer]-[StartOD] AS RunningSum
FROM qry_ODO_TotalSub
ORDER BY qry_ODO_TotalSub.ID;

thanks for looking

 

[plog]

That's a bad way to do a running sum. DLast and DFirst should be removed, not just from your code but from Access--they are horrible functions.

A running sum should be done with a DSUM (http://www.techonthenet.com/access/functions/domain/dsum.php). The criteria of which should be all records that should be grouped together and limited by an ordering field.

You've said you are grouping by vehiclenum, but which field are you ordering by? Can you post some sample starting data, then what data you expect your running sum query to return based on that starting data?

 

[foshizzle]

Looking at DSUM now.
Here is the sample

 

[plog]

This should do it:

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, 1*DSum("[Odometer]","tblOdometer","[VehicleNum]='" & [VehicleNum] & "' AND ODate<=#" & [ODate] & "#") AS RunningSum
FROM tblOdometer;

 

[foshizzle]

Wow.. Definitely much simpler and easier to keep up with.
Thank you