Running Sum by Date criteria in a query

[inno]

Hello!


I want to running sum with Date and CustomerID criteria. My data is something like this;

CustomerID	VDate		Debit
1	30-06-12		50,000.00
1	02-07-12		6,400.00
1	02-07-12		24,030.00
1	02-07-12		63,750.00
2	03-07-12		2,072.00
2	04-07-12		17,480.00
1	05-07-12		3,625.00
1	11-07-12		2,560.00
1	11-07-12		4,080.00

I want running sum of current field for all previous date debit fields for customerID. I don't know how to do this, moreover there is also problem of duplicate dates. Because duplicate date can cause problem while summing the older date fields if the current date is also same.
I look forward to any assistance.

[alansidman]

This thread describes a technique I have used for creating a running sum in a checkbook. You may have to add the record number ID (hopefully an autonumber) to the mix.

http://www.mrexcel.com/forum/microso...l-records.html

Alan

[inno]

Thanks for your help.
After going through the thread, i could not find solution for my problem. In my case i want a running sum in query by the vDate field for a perticular account ID but i fail. I can easily get running sum by the transaction ID, but when i add a new record in back dates, it just gives running sum on the basis of transaction id. Another thing, for instance if i succeed getting running sum by date, i don't know what to do with duplicate dates.

This thread describes a technique I have used for creating a running sum in a checkbook. You may have to add the record number ID (hopefully an autonumber) to the mix.

http://www.mrexcel.com/forum/microso...l-records.html

Alan

[alansidman]

In my checkbook program which calculates on the dates, if there is more than one date, then the result is the same for all records having the same date. This is not an issue for me. If there are duplicate dates, what would you like Access to do about it?

Here is another solution, but I don't think it will solve your issue with the duplicate dates. You will need to resolve in your mind, which is more important to you. Dates or Transactions. Without some substantial VBA which would loop through your recordset and characterize your data based first on date and then on transaction ID, I am not sure that there is an alternative solution.
http://www.datapigtechnologies.com/f...unningsum.html

Alan

[inno]

Thanks allot.
Actually i am working on A/R Aging report. For the purpose i need running sum based on date fields than by using IIF statement i can apply the payments, but i m just wandering around the running sum. I can get the running sum in report directly but i can not sum up in the footer as calculated fields can not be sum up. That is why i have to do it through query. Currently i get running sum through this
RunningSum: CCur(Nz(DSum("[Debit]","[Debit]","[CustomerID] =" & [CustomerID] & " AND [IDtrans] < " & [IDtrans] & ""))).
IDtrasn is the auto-number for Debit table. There is a field vDate in Debit table, that i want to include in the above Dsum function, or there may be some other best way to do it.

In my checkbook program which calculates on the dates, if there is more than one date, then the result is the same for all records having the same date. This is not an issue for me. If there are duplicate dates, what would you like Access to do about it?

Here is another solution, but I don't think it will solve your issue with the duplicate dates. You will need to resolve in your mind, which is more important to you. Dates or Transactions. Without some substantial VBA which would loop through your recordset and characterize your data based first on date and then on transaction ID, I am not sure that there is an alternative solution.
http://www.datapigtechnologies.com/f...unningsum.html

Alan

[alansidman]

Help me to understand what goes in the footer? I cannot understand how you would use a running sum in a footer? Try to explain in business terms what you are attempting to accomplish.

[inno]

Dear alansidman'
I just want to have a running sum in query for table Debit, 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 the smaller than the current date field, something like this

CustomerID	VDate		Debit	RunningSum
1	30-06-12		50,000.00	0
1	2/7/2012		6,400.00	50,000.00
1	2/7/2012		24,030.00	56,400.00
1	2/7/2012		63,750.00	80,430.00
2	3/7/2012		2,072.00	0
2	4/7/2012		17,480.00	17,480.00
1	5/7/2012		3,625.00	144,180.00
1	11/7/2012		2,560.00	147,805.00
1	11/7/2012		4,080.00	150,365.00

Help me to understand what goes in the footer? I cannot understand how you would use a running sum in a footer? Try to explain in business terms what you are attempting to accomplish.