Line by line sum of two columns in a report

I'm setting up a checkbook register db and in the table I have a deposits
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12719-line-line-sum-two-columns-report.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
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.

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

H2OBOWL wrote:
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719

Make sure you are sorting and grouping properly. How do you have them
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
grouped and sorted now?

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



--
Joseph Meehan

Dia duit

On Thu, 6 Jul 2006 14:31:02 -0700, H2OBOWL
wrote:

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
this field.

John W. Vinson[MVP]

Thanks. How/where should I put this into the query?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719

"John Vinson" wrote:

> On Thu, 6 Jul 2006 14:31:02 -0700, H2OBOWL
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
> wrote:
>
> >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]
>

On Thu, 6 Jul 2006 1602 -0700, H2OBOWL
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
wrote:

>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]

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" wrote:

> On Thu, 6 Jul 2006 1602 -0700, H2OBOWL
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
> wrote:
>
> >Thanks. How/where should I put this into the query?
>
> Create a new Field by typing the expression in a vacant Field cell in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
> the query grid.
>
> John W. Vinson[MVP]
>

On Thu, 6 Jul 2006 20:47:02 -0700, H2OBOWL
wrote:

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719
>anything under normal circumstances.

Ah. Sorry, should have realized that!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12719

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]