Running sum by date

[Ray67]

Hello



I would like to get running sum. Here is the example

Date DepositAmount WithdrawalAmount RunningSum
10/01/2012 0.00 225.65 -225.65
10/03/2012 775.00 0.00 549.35
10/04/2012 775.00 0.00 1324.35



Thank you

[June7]

This can easily be done in reports. Textbox on report has a RunningSum property.

Calculate the difference of Deposit and Withdrawal in query or report textbox, set RunningSum property of textbox that holds the calculated value.

[Ray67]

Thank you for your reply. After posting thisthread i notice i should do this in reports. I just posted a new thread inreports. How do i get last month ending balance into current month???

Date DepositAmount WithdrawalAmount RunningSum
Beginning balance 200.00-(This is September balance)
10/01/2012 0.00 225.65 -25.65
10/03/2012 775.00 0.00 749.35
10/04/2012 775.00 0.00 1524.35

Thank you

[June7]

Can do a nested subquery that sums the data through September. Review the example for Year To Date in http://allenbrowne.com/subquery-01.html

I deleted the other thread as it just duplicates this one.

[Ray67]

June7

I looked at the link. Sql is a foreign language to me. Do you have an other links i can look at???


Thank you

[June7]

Access query objects use SQL. Open in SQL View and study the sql statement. That is one way to get familiar with SQL structure. Another is tutorials. Here is one site http://www.w3schools.com/sql/default.asp

[Ray67]

June7

I got it to work.
YTD: DSum("DepositAmount","Query2","MonNum>='" & [Newdate] & "'")

How can i subtract WithdrawalAmount field???

[June7]

I never had to build report like this (I don't work with accounting type databases). Beginning/Ending balances not something I have to deal with.

But think this is possible method:

Have the beginning balance calculated in a textbox in report or group Header.

Then calculate ending balance in a textbox in report or group footer. Something like:

= Me.textboxBeginning - Sum(DepositAmount - WithdrawalAmount)

However, don't know if a running sum that includes the starting balance can be accomplished.

[Ray67]

So the query is not working right. Its not giving me running sum by month and year.

Can any one else help me????

YTD: DSum("DepositAmount","Query2","MonNum>='" & [Newdate] & "'")

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[Ray67]

Sure. look at query3


Copy of 720.mdb


Thank you

[rpeare]

YTD: DSum("DepositAmount","Query2","MonNum>='" & [Newdate] & "' and Year = '" & [year] & "'")

I'd strongly advise you not to do running sums in queries though, particularly with large datasets. It is incredibly inefficient when compared to doing the same function on a form or report.

[Ray67]

rpeare

Thank you for your reply. I really need this on reports. How can i do it in reports??? Also need the report to start with a text box showing previous month balance. The user would run report by month.

[Ray67]

rpeare

I did the running total in reports. So all i'm missing is how to show previous month balance.

Thank you

[June7]

Activate the Report Header section.

Create a textbox in that section.

Expression in textbox ControlSource:
=DSum("[DepositAmount]-[WithdrawalAmount]","T_Transactions","[TransactionDate]<#11/1/2011#")

Notice the static date criteria. Making this dynamic is another issue. Can be a reference to textbox on form.

The report is bound to a query that is filtered only by DateCleared. This means duplication of data that was used in the StartBalance calc.