Running Sum

[BringTheR41n]

I am trying to convert all of my data from excel files to a single access database. Im trying to work on a way to organize my bills for my company. I have a table in excel that has specific formulas that apply only to a certain row but the formula is carried throughout the column, ie: the daily balance = previous daily balance + deposits - bill. and the number it gives stays with that one row and all of my previous balances are still in the column. I have read about running sums and have no idea on how to use/apply them and wondered if there is any way that they apply to my circumstance. Is there a way to have this in an access query/report that reads almost like a bank statement? i also have bills that are due on a future day in the month and they display a projected daily balance.


Also I check my bills and bank account daily for my actual balance available and would like to incorporate the daily balance in another column. would there be a way that the function: if(date=today,totalbalance,0), would be a valid way to accomplish that?
Thanks for the help.

[June7]

Related thread https://www.accessforums.net/queries...ces-45273.html

Bing: Access bank ledger
http://office.microsoft.com/en-us/te...001017534.aspx

Why build from scratch? OTS programs available, like Quicken and QuickBooks.

[rpeare]

Running sums should be done on a report, trying to do them in queries is extremely memory intensive and, as your database grows, become very time consuming to run.

So let's say you are trying to print a statement for a particular person for a particular time period, your report would consist of 3 things.

1. Determining what their balance was before they started the period.
2. Add credits, subtract debits during the period
3. Calculate the ending balance from the sum of the first two.

Item 1 can be determined by it's own AGGREGATE query with a criteria of < (less than) the starting date of the period which can be set up as a criteria in a query of something like:

< "#" & [Enter the Start Date] & "#"

or if you are trying to inherit a date range form a form

< "#" & [forms]![formname]![startdate] & "#"

if you've correctly created an aggregate query you should be able to come up with their balance just prior the period you're reporting on.

Then you can create a second query that shows the detail for the customer and link in the query you just ran (or create a report/subreport structure) and in the DETAIL section you'd have a field where the CONTROL SOURCE was something like

=[Deposit] + [Bill]

and in the RUNNING SUM property put OVER GROUP (this will follow the same grouping structure as your report so if you are grouping by customer it will also)

So you can show your deposits, your bills and the running sum can include their 'starting balance' for the period as well.

Then finally in your report or group footer (depending on how you're doing your processing) you can have a final formula for the 'ending balance'