I have two columns, How do I have a running total in column 2

P

Phil Dalton

I have two columns, I'm entering a number in column 1 weather +/- and
wanting a running total in column 2. Like a checkbook register.
 
K

KARL DEWEY

Plus or minus what?

Running total of what in column 2? Would not a "checkbook register" give
you a balance, not total?
 
P

Phil Dalton

It would go something like this...

column 1 column 2
1000 1000
500 1500
-300 1200

and so on. Any ideas.
 
G

Graham Mandeno

Hi Phil

You can do this easily with a report, by using the RunningSum property for a
textbox.

For a form (or query) it is only possible if your table includes some
unique, sortable field that can be used to identify those records which
occur before the current record. This could be a date/time field (provided
you can't have two records with exactly the same transaction date/time) of
it could be a unique, ascending transaction number. Your query field
expression would be something like this:
=DSum( "AmountField", "TableName", "TransNum<=" & [TransNum])
 
K

Ken Sheridan

Firstly, you should not store the running balances as a column in the table
but compute them in a query when required. Storing data computed form other
data leaves the door open to update anomalies as one can be changed
independently of the other. Before you can compute the running balances,
however, you need a field in the table by which you can order the data. If
you want it sorted in the order of input the best way is to include a
DateStamp field in the table with a default value of Now(). This will
automatically enter the date/time when a new record is entered. Don't be
tempted to think you can use an autonumber field for this; an autonumber
guarantees uniqueness, not necessarily sequence.

The usual way to compute the running balance would be by a subquery which
references the outer query so that it sums all rows up to and including the
current one, e.g.

SELECT MyField,
(SELECT SUM(MyField)
FROM MyTable As T2
WHERE T2.DateStamp <= T1.DateStamp) AS RunningBalance
FROM MyTable AS T1
ORDER BY DateStamp;

Queries in Access which include SQL aggregate functions are not updatable
however, so if you need the query to be updatable you should use the VBA DSum
function rather than the SQL COUNT function:

SELECT MyField,
DSum("MyField","MyTable","DateStamp <= #" & Format([DateStamp],"mm/dd/yyyy
hh:nn:ss") & "#") AS RunningBalance
FROM MyTable
ORDER BY DateStamp;
 
Top