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
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