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

    Running Sum without Unique Record (2003 SP2)

    I have a table that I am using in a report (see attached Excel sample). I want to sort by baty, clientwiptotal, then clnum while calculating a running sum of the clientwiptotal for each baty. I tried using the Running Sum in the Report, but because some of my clientwiptotal numbers are the same, it ignores those records and doesn't give me the correct running sum. I was going to try to use the Running Sum in Query to get around that, but am having problems with that as well. Is there any way to have the Report ignore a duplicate record (in this case the same clientwiptotal) and add it to a running sum for that group? Thank you in advance. Norma
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    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 15 Times in 15 Posts

    Re: Running Sum without Unique Record (2003 SP2)

    I don't understand. If you include clnum in the report, you'll include the duplicate values of clwiptotal too. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Or do you want to exclude the duplicate values from the running sum? (As far as I can tell, that is the opposite of what you ask)

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

    Re: Running Sum without Unique Record (2003 SP2)

    Actually I only included the group sorts that are on the report in order to summarize my problem. There are actually many lines that make up the client totals (in other words, for each clnum, there could be a number of matter items). I have already added the results of a query that calculates the total for each client because I need to show the client totals in descending order for each baty. My report sort is baty, clientwiptotal, clnum so that I can show each baty (a person), their client totals in descending order, then the actual detail of the clients (which is below the clnum and shows all the matter items).

    Sorry if this is confusing. I know I am at my wits end <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Running Sum without Unique Record (2003 SP2)

    I'm afraid I fail to grasp the problem. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Running Sum without Unique Record (2003 SP2)

    Thank you so much for your efforts. The gist of the problem is that I am trying to show the % of the Client WIP Total as I display them in descending order for each person's ID#. But when the amount is the same, it doesn't calculate -see page 208 in the Report for an example. Norma
    Attached Files Attached Files

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

    Re: Running Sum without Unique Record (2003 SP2)

    I don't see anything special on page 208. Can you give a specific baty/wiptotal for which the result is not what you want?

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

    Re: Running Sum without Unique Record (2003 SP2)

    Yes, if you change the clienttotalwip Footer to visible, you will see the cumulative total for each baty, client. This lengthens the report, but starting on the new page 256 for baty# 01391 starting with clnum 53221 to clnum 63256 you will see that the % is not calculating correcty. When I get to clnum 63256 the cumulative amount should be $167,620.79 divided by baty 01391 total of $170,421.65 which is 98.4%, but it keeps repeating the prior 98.1% because it thinks it is still $167,148.29/$170,421.65.

    This happens whenever the client wip total is the same as the prior or next one. I need the cumulative balance to continue to grow so I can accurately show the % of the baty total.

    I hope I explained this correctly. Thanks so much! Norma

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

    Re: Running Sum without Unique Record (2003 SP2)

    I've been looking at it, but I'll be away from my PC for a while. I'll come back to this problem later on.

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

    Re: Running Sum without Unique Record (2003 SP2)

    As far as I can tell, the solution is to place the text box Text71 in the group footer for clnum (which can be hidden) and to set the Group Header and Group Footer properties of clientwiptotal to No, i.e. don't group on that field, just use it to sort descending.

    See the attached version.
    Attached Files Attached Files

  11. #10
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum without Unique Record (2003 SP2)

    Sorry for the delay in my response - I was pulled away on another project, but I wanted to thank you. As usual, you have saved the day and taught me something new. Thank you so much, Norma

Posting Permissions

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