Running Balance

J

jez123456

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
 
N

Nikos Yannacopoulos

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
 
C

Chris E.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top