Runnning Total

[ELL]

Hello,

I am attempting to calculate a running total in a query, and although I have spent the past few hours googling this topic, I am unable to make it work.

I have a table where all deliveries are recorded with a field for date/time [Arrive Customer] and delivery volume [Delivery Volume]. There can be multiple deliveries in each day, but in that case they will have different times (impossible for two deliveries to occur at the exact same time). Hence [Arrive Customer] field will have unique values.
(e.g. 06-Jan-12 21:34)

I want to create a query that reports the deliveries summed per day, with a running total.

Column 1: Day
Column 2: Sum of delivery volumes on that day
Column 3: Running total of delivery volumes

I can make a query for the first 2 columns easily, as example below (I use the DateValue function to discard to time component of the date)

SELECT
DateValue([Arrive Customer]) AS DeliveryDay,
Sum(tbl_Journey.[Delivery Volume]) AS SumDailyDeliveries
FROM tbl_Journey
GROUP BY DateValue([Arrive Customer]);

I have attempted to insert a DSum field to create the running total but have been completely unsuccessful.
e.g.
DSum("Delivery Volume]","tbl_Journey","[Arrive Customer]<=" & [DeliveryDay])

Any assistance is most appreciated!

Cheers

 

[Alansidman]

This link may help you to understand how to do running sums.

http://www.mrexcel.com/forum/showthread.php?t=358307&highlight=Running+Sums

 

[ELL]

This link may help you to understand how to do running sums.

I was unable to make it work in the "normal" way with DSum, so in the end I found a temporary, convoluted method of creating a separate table with all required info and running an UPDATE query. I know it's not supposed to be done this way, but it works. I suspect one of the issues is that I don't have a unique identifier for each line, as the running total is to be run on the sum of each day.

Cheers

 

[Alansidman]

If it works, then Bravo. Glad you figured it out.