Line by line sum of two columns in a report

[Guest]

I'm setting up a checkbook register db and in the table I have a deposits
field and a withdrawals field. On the report I want to sort by date and have
a line by line balance to the side of each transaction the same way it would
be in a paper checkbook register. I can't seem to get running sums to work
for the two fields.
Thank you for your help.

 

[Joseph Meehan]

I'm setting up a checkbook register db and in the table I have a
deposits field and a withdrawals field. On the report I want to sort
by date and have a line by line balance to the side of each
transaction the same way it would be in a paper checkbook register.
I can't seem to get running sums to work for the two fields.
Thank you for your help.

Make sure you are sorting and grouping properly. How do you have them
grouped and sorted now?

What exactly is it doing or not doing that is the problem?

 

[John Vinson]

I'm setting up a checkbook register db and in the table I have a deposits
field and a withdrawals field. On the report I want to sort by date and have
a line by line balance to the side of each transaction the same way it would
be in a paper checkbook register. I can't seem to get running sums to work
for the two fields.
Thank you for your help.

Running Sum works *across records*, not *across fields*.

Instead you can put a calculated field in the query:

LineBalance: [Deposits] - [Withdrawals]

to get the net amount of this transacation, and use the running sum on
this field.

John W. Vinson[MVP]

 

[Guest]

Thanks. How/where should I put this into the query?

I'm setting up a checkbook register db and in the table I have a deposits
field and a withdrawals field. On the report I want to sort by date and have
a line by line balance to the side of each transaction the same way it would
be in a paper checkbook register. I can't seem to get running sums to work
for the two fields.
Thank you for your help.

Running Sum works *across records*, not *across fields*.

Instead you can put a calculated field in the query:

LineBalance: [Deposits] - [Withdrawals]

to get the net amount of this transacation, and use the running sum on
this field.

John W. Vinson[MVP]

 

[John Vinson]

Thanks. How/where should I put this into the query?

Create a new Field by typing the expression in a vacant Field cell in
the query grid.

John W. Vinson[MVP]

 

[Guest]

It's not working. The Line Balance only shows blanks on the report. I found
that the Line Balance only shows anything on the report when there is an
amount in both the deposit and withdrawal fields, and since there is no
reason to have a deposit and a withdrawal on the same line it doesn't show
anything under normal circumstances. (Also when I run the query by itself
the Line Balance column is all blank) I figured that *might* be ok though
and I went ahead and set up my running sum but no good. The running sum just
shows zeros all the way down. My guess is that since there is no data in the
Line Balance field then the running sum figures the answer is zero. It looks
like the line balance isn't working for what I'm needing. What do you think?

 

[John Vinson]

It's not working. The Line Balance only shows blanks on the report. I found
that the Line Balance only shows anything on the report when there is an
amount in both the deposit and withdrawal fields, and since there is no
reason to have a deposit and a withdrawal on the same line it doesn't show
anything under normal circumstances.

Ah. Sorry, should have realized that!

Use this expression instead (or an analogous one):

LineBalance: NZ([Deposits]) - NZ([Withdrawals])

The built-in NZ (Null To Zero) function will convert blank values to 0
and they'll add correctly.

John W. Vinson[MVP]