CODEWITHSUNDEEP
ms-access
Shabbir
Shabbir

Reputation: 23

Running Sum by Date criteria in a query (Duplicate Dates)

I want to obtain a running sum in query. I'm using the following running sum formula

RunningSum: CCur(Nz(DSum("[Debit]","[Debit]","[CustomerID] =" & [CustomerID] & " AND [vDate] < " & [vDate] & "")))

But it is not working. My purpose is to obtain sum of Debit for all smaller than the current date field, something like this,

https://i.sstatic.net/0qoO7.jpg

After going through different threads, I could not find any solution for my problem. I don't know that how I can get the sum of older debit amounts if there is duplicate date.

Upvotes: 2

Views: 3363

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

I think the easiest thing will be to just refer to running sums for all the controls you want to add. For example "31 to 60 Days" is text29 on your report. Create a hidden control called, say, R31to60 and set to Running sum over group, then in the footer, put a text box and set the control source to:

=[R31to60]

It will show the last value for the running sum, that is, the total.

In design view, the highlight shows the running sum control and total. The control can be shrunk down and hidden.

Running sum design

In report view you can see the "total" field shows the last value for running sum.

running sum report

Upvotes: 1

Related Questions