Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Sum in Group Footer (2003 SP2)

    I am showing detail for a group that keeps a running sum on an amount field. I have calculated a % threshold in a field and when the running sum goes over that $ amount I have made the line not visible. However, I would like to show this last $ amount below the threshold in the group footer. This threshold changes as I go through the next member of the group but of course the running sum keeps accumulating the amounts even though the lines are not visible. Does anyone have any ideas how to capture the sum or just the amounts that are visible? Thanks in advance, Norma

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    Place an unbound text box txtTotal in the group footer.
    Add code similar to the following to the On Format event of the detail section, subsituting the appropriate names:

    If Me.txtRunningSum <= TresholdValue Then
    Me.txtTotal = Me.txtRunningSum
    End If

    The value of txtTotal is set to that of the running sum as long as that hasn't passed the treshold.

  4. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    That works for the first person in my group, but the txtTotal stays the same for the next person in the group and I need that to start fresh with the new threshold value. Now my report prints correctly for the first person but is blank for the remaining people in the group because the txtTotal remains the same. Is there a way to reset txtTotal = 0 as it makes its way through the group? Thank you! Norma

  5. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    Did you chose to have the field accumulate as a running sum "over group," or "over all?"

  6. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    I set it to Over All because if I just do the Over Group it doesn't calculate properly. - it just gives me the same total as the field. My report sort is by person, then clients (that's the running total). Thank you, Norma

  7. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    Well, I think if Han's method works fine for the first person, it should work for the next -- if you do use the running sum over group. I think (maybe I'm not figuring this out correctly) but I think you really must have a "running sum over group," so it can restart at zero for each client -- unless you want to do that all in code. If the "over group" isn't working, you might want to re-check your sorting?

    Ignore this that I wrote earlier , but just leaving it in FYI: How to you make the lines invisible -- IOW, since you've probably got some code to make the line invisible, can you set a public variable at that time that (preventing it from incrementing further while with the current client is the same) and stores the amount you want to display in the group footer, and and then display it? and, upon the client changing, you set this public variable back to zero?


    Pat

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    The method I described assumed that you wanted a running sum per group, so the Running Sum property should be set to Over Group. I don't understand what you want to do if it is set to Over All. Could you provide more detailed information?

  9. #8
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    I actually have four group sorts so I made another running sum and changed the code appropriately. It now seems to be working. Thank you both so much!! Norma

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Running Sum in Group Footer (2003 SP2)

    Great! Glad it's working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •