Sum a column with duplicate data 2

[AZdesertdog]

CR v.9 MS SQL Server DB

Trying to find a way to properly calculate a column that has duplicate data.

Existing Data- (group header report- details suppressed)

Date Inv.Count Balance
GH1> 12/1/2003 2 (need this formula)

When I drill down into the above to see the two invoices, I have multiple transaction lines for each invoice number and the balance is always the same on each line but different for the two inovices in this example.

Details> 12/1/2003 12345 $125.00
12/1/2003 12345 $125.00
12/1/2003 12345 $125.00
12/1/2003 12345 $125.00
12/1/2003 12345 $125.00
12/1/2003 12444 $200.00
12/1/2003 12444 $200.00
12/1/2003 12444 $200.00
12/1/2003 12444 $200.00

Desired results..

Date Inv.Count Balance
GH1> 12/1/2003 2 $325.00

Basically, sum the balance only once for each invoice number. I tried suppressing if duplicate on the balance rows but summing still adds all rows. Any help appreciated!
-JJP

 

[lbass]

Insert a running total, using the running total editor. Select {table.amt}, sum, evaluate on change of field {table.invoiceno}, reset on change of group.

-LB

 

[BobSuruncle]

JPOULOS,make sure that you place the Running Total in the Group Footer and not the header, as your example seems to indicate. Running Totals are 2nd pass objects and so are only going to be accurate in the Footer sections.

HTH


Bob Suruncle

 

[lbass]

Thanks, Bob, forgot to mention that.

-LB

 

[AZdesertdog]

Thanks to both! Bob- it's funny you wrote- I was having exactly that problem. Thanks to you and LB! -JJP