Running sum for time and group?

[giffordpinchot]

Hello,

I'm trying to calculate some running sum queries in preparation for some charts on a report.

I have:
tbl: Spencerdaily
fld1:datein
fld2:milesdriven
fld3:vehiclenumberdaily

I am trying to calculate the running sum of milesdriven for each year per vehicle. If I use the following I get the same set of sums repeated for each vehicle:

SELECT spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year]) AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);

I tried this but it didn't work:

SELECT spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily AS vehiclealias, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year] & " AND [vehiclenumberdaily]<=" & [vehiclealias] & " ") AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);

What am I doing wrong?

Thanks,
Gifford

 

[Simon_MT]

I would suggest not using DSum and just summing the records.

 

[Jon K]

Assuming VehicleNumberDaily is a text field, try this query.

SELECT VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year],
DSum("milesdriven","spencerdaily","VehicleNumberDaily='" & VehicleNumberDaily & "' and DatePart('yyyy', [datein])>2001 and DatePart('yyyy', [datein])<=" & [Year])+0 AS Mileage
FROM spencerdaily
GROUP BY VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING DatePart("yyyy",[datein])>2001;
.