Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Line by line sum of two columns in a report

Discussion in 'Information Technology' started by H2OBOWL, Jul 28, 2006.

  1. H2OBOWL

    H2OBOWL
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Khalsa Aid The Selfless Sikh Faith On The Frontline, A Documentary By BBC 2016 Sikh Organisations Nov 16, 2016
    Sikh News Veteran Sikh Soldiers To Inspire Young - Malay Mail Online Breaking News Aug 1, 2016
    Hard Talk Confused Islamophobes Target American Sikhs Online Hard Talk Apr 26, 2016
    Is There A Thin Line Between Love And Hate? Interfaith Dialogues Apr 25, 2016
    Hard Talk Taking One Liners Out Of Context From Gurbani To Allow Intellectual Independence? Hard Talk Apr 23, 2016

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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.


    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?



    --
    Joseph Meehan

    Dia duit
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 6 Jul 2006 14:31:02 -0700, H2OBOWL
    <H2OBOWL@discussions.microsoft.com> 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]
     
  5. H2OBOWL

    H2OBOWL
    Expand Collapse
    Guest

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

    "John Vinson" wrote:

    > On Thu, 6 Jul 2006 14:31:02 -0700, H2OBOWL
    > <H2OBOWL@discussions.microsoft.com> 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]
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 6 Jul 2006 16:13:02 -0700, H2OBOWL
    <H2OBOWL@discussions.microsoft.com> 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]
     
  7. H2OBOWL

    H2OBOWL
    Expand Collapse
    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" wrote:

    > On Thu, 6 Jul 2006 16:13:02 -0700, H2OBOWL
    > <H2OBOWL@discussions.microsoft.com> 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]
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 6 Jul 2006 20:47:02 -0700, H2OBOWL
    <H2OBOWL@discussions.microsoft.com> 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
    >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]
     

Share This Page