Running Balance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a table with the following columns.

Credit Debit
£0.00
£400.00
-£20.00
-£20.00
£100.00
-£50.00

I need to create a query that will calculate a running balance like:

Credit Debit Balance
£0.00 £0.00
£400.00 £400.00
-£20.00 £380.00
-£20.00 £360.00
£100.00 £460.00
-£50.00 £410.00

Many thanks
 
You are obviously thinking Excel!!! If you have a good reason for doing
this in Access, then you should start by improving your design. All
amounts should be in *one* field (Access tables have fields, not
columns), and you can either use the sign to differentiate between
credits and debits, or, if you prefer, add an extra field to mark
amounts as credit/debit (in which case you don't need the signs, but can
keep them if you wish).
In any case, getting a running sum in a query can be tricky if you don't
have a PK field in your table, but maybe it isn't required at all! I
assume that you would need that on a report really, where it is easier
to get it by means of a textbox control's Running Sum property.

HTH,
Nikos
 
Try this. This is a 2 field table. Field 1 is Check# and Field 2 is Amount.
Build a select or make table query.

Select both fields in the table and put this in the third fieldname box...

Accumulated: DSum("Value","[Table1]","Check#<=" & [Check#])

That should work.
 
Back
Top