Hi,

I need to remove duplicated values from a Crosstab after joining two tables. The values need to be removed for a total, not just suppressed so they can't be seen.

This is a problem I've been working on for two weeks and all the topics I've found related use the COUNT or DISTINCTCOUNT rather than the SUM that I need.

I'm connecting two tables of information: SvcTbl and PayTbl. Individually, the Aging Crosstab comes out correctly, but when I connect them in any way the service amount (SvcAmt) total increases as there are multiple payments (PayAmt) for each service.

The tables are setup so that under SvcTbl each patient has a Claim# and a Charge# for a SvcAmt. PayTbl is setup the same way, but there are mulitple PayAmt for each Charge# (from adjustments and payments from different insurers).

What happens when I join the tables is that all the extra claim numbers populate in the SvcTbl where there had been one before so I'm getting the same charge many times.

Here's how it boils down visually:

SvcTbl
              Claim#   Charge#    SvcAmt
Tom                1               1           10
Tom                1               2             2
Harry              1               1           10
Harry              2               1           10
                                                    32

PayTbl
              Claim#   Charge#    PayAmt
Tom                1               1             -3
Tom                1               1            +3
Tom                1               1            +2
Tom                1               2             -2
Harry              1               1             -8
Harry              1               1            +6
Harry              2               1             -4

SvcTbl Connected to PayTbl
              Claim#   Charge#    PayAmt    SvcAmt
Tom                1               1             -3           10
Tom                1               1            +3           10
Tom                1               1            +2           10
Tom                1               2             -2             2
Harry              1               1             -8           10
Harry              1               1            +6           10
Harry              2               1             -4           10
                                                                     62

I've tried Select Distinct Records, but that removes PayAmts. I've also made a few functions, but the Crosstab won't allow me to use one that declares a NumberVar.

I'm making the Aging Crosstab by Service and Payment totals per Facility for each Month.

If anyone could help me I'll greatly appreciate it.


Edited by Paladin42 - 14 Aug 2009 at 9:07am