Skip to Content
0
Former Member
Dec 05, 2008 at 04:25 PM

ignoring duplicates when summing

35 Views

Got some data like this:

BatchID, Batch Desc, PayerType, PayerName,  MRN, PaymentAmt, AppliedAmt, PaymentType
 2,       1002,        Patient,   Patient1,  264, 130.00,     130.00,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,       0.17,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,       0.19,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,      11.32,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,      11.72,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,      12.34,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,      16.53,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,      38.31,     Payment
 2,       1002,        Patient,   Patient2, 1332, 100.00,       9.42,     Payment
 3,       1003,        Patient,   Patient3, 3674,  60.00,      14.94,     Payment
 3,       1003,        Patient,   Patient3, 3674,  60.00,       8.12,     Payment
 3,       1003,        Patient,   Patient3, 3674,  60.00,       6.94,     Payment

I am grouping on BatchID and MRN

Have a Sum in the Group footer on PaymentAmt for the BatchID groups and

AppliedAmt for the MRN groups.

The values for the data above are showing as

Sum of PaymentAmt =  930.00 (for BatchID 2 group)
Sum of PaymentAmt =  180.00 (for BatchID 3 group)
Sum of AppliedAmt =  130.00 (Patient1)
                      88.28 (Patient2)
                      30.00 (Patient3)

I'd like it to read:

Sum of PaymentAmt = 230.00 (for BatchID 2 group)
Sum of PaymentAmt =  60.00 (for BatchID 3 group)
Sum of AppliedAmt = 130.00 (Patient1)
                     88.28 (Patient2)
                     30.00 (Patient3)

How do I achieve this?

Edited by: Eddie Shipman on Dec 5, 2008 5:26 PM