Re: Calculating a new balance for each line item reported

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 11:37:55 +0800

Use Running Sum.

This example assumes you have a text box named txtOpenBal in the report
header that has the starting balance (original deposit?).

1. Add a text box to the Detail section, and set these properties:
    ControlSource =[payment]
    Running Sum Over Group
    Visible No
    Name txtPaymentRS

2. Add another text box so show the balance. Set these properties:
    ControlSource =[txtOpenBal] - [txtPaymentRS]
    Format Currency 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"CLM" <CLM@discussions.microsoft.com> wrote in message
news:35A1FC47-3282-4D16-A0E5-B5D9A290E8CF@microsoft.com...
>
> I have Excel spreadsheet that does the following:
>   deposit = 10,000.00
>      date      payment  Avail Bal
>     1/1/04     500.00   9,500
>     2/1/04     500.00   9,000
>     3/1/04     250.00   8,750
>     4/1/04   1000.00   7,750
>
> Basically how do I do that in a report where we do NOT have the Avail Bal
> stored.  It has to be calculated either in the report query or in the 
> report
> sql code.  Any ideas?
>
> Thx

Relevant Pages

  • Re: Eliminate zero values/pass calculated value to new calc field
    ... In my report, I have a Calculated field that sums Beg.Balance and Deposit Amt, less fee amt and/or check amt. ... But then each successive period Ending Balance is off, because the calc still uses the original Beg.Balance amt. ... If there's too much detail here, you might at least consider using the "Running Sum" feature of an Access Report, though you can't do any calculations using it. ... The Queries listed here form the basis for a Report that lists individual transactions by date, running balance for the date, and ending balance. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Eliminate zero values/pass calculated value to new calc field
    ... go a simpler method of just doing a running sum, however I can't seem to get ... but every transaction after that is off because it uses the Beginning Balance ... I tried in the report to add an unbound text ... >>>starting value for the account. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Calculate beginning balance, but print details on report
    ... beginning balance based on all transactions prior to the beginning date. ... The check book report can be run for: ... it needs to calculate the beginning balance by simply ... Then the report needs to print the checks and deposit for the ...
    (microsoft.public.access.reports)
  • Re: Running balance and balance b/f
    ... Actually I have used the Running Sum without any success, ... take into consideration the previous amount - balance brought forward (all ... > Using queries I can find the total deposits and total withdraw amounts ... > transaction, shown on the report. ...
    (microsoft.public.access.reports)
  • Re: Showing previous balance and current billed balance
    ... In your report, add the Amount text box twice. ... The text box now gives you a running sum, which is the current balance. ... If the Account Number is a Text field, ...
    (microsoft.public.access.reports)