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

2008-05-21, 16:00

HansV

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)

2008-05-21, 16:36

nthayer

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>

2008-05-21, 16:49

HansV

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.

2008-05-21, 17:15

nthayer

1 Attachment(s)

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

2008-05-21, 17:33

HansV

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?

2008-05-21, 17:56

nthayer

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

2008-05-21, 18:34

HansV

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.

2008-05-21, 22:11

HansV

1 Attachment(s)

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.

2008-05-23, 13:40

nthayer

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