removing records that look like duplicates but aren't

[Freida]

I have a query that finds duplicate paid claims. It works in about 85% of
the time though. I am finding duplicates based on the following criteria.

Member Id
date of Service
Requested Amount
Claim Provider

As I stated before, this works most of the time but the instance where it
doesn't work is this:

A doctor sends in a claim with 5 charge lines- We pay 3 of the charge
lines. Then the doctor resubmits the claim b/c he wants to get paid for the
other 2 charge lines and we pay the other 2 charge lines. This fits the
above the criteria but it is not a duplicate claim. How can I get these
records out. I cannot do it by chargeline b/c if it is after 30 days an
extra chargline line is added for interest so if it is a duplicate the
chargeline numbers won't match.

Any help with this will be greatly appreciated.

 

[John W. Vinson]

I have a query that finds duplicate paid claims. It works in about 85% of
the time though. I am finding duplicates based on the following criteria.

Member Id
date of Service
Requested Amount
Claim Provider

As I stated before, this works most of the time but the instance where it
doesn't work is this:

A doctor sends in a claim with 5 charge lines- We pay 3 of the charge
lines. Then the doctor resubmits the claim b/c he wants to get paid for the
other 2 charge lines and we pay the other 2 charge lines. This fits the
above the criteria but it is not a duplicate claim. How can I get these
records out. I cannot do it by chargeline b/c if it is after 30 days an
extra chargline line is added for interest so if it is a duplicate the
chargeline numbers won't match.

Any help with this will be greatly appreciated.

No way to tell based on what you've posted! What's the actual data in your
table? Is each of these five charge lines a record in this table? How would
you MANUALLY determine whether a record is or is not a duplicate?

 

[Freida]

No way to tell based on what you've posted! What's the actual data in your
table? Is each of these five charge lines a record in this table? How would
you MANUALLY determine whether a record is or is not a duplicate?

Claim 1

Claim # Member ID Date of Serv Prov chline # Amt Req. Amt
pd.
234 417 2/14/09 Mary 1
300 45
234 417 2/14/09 Mary 2
100 5
234 417 2/14/09 Mary 3
800 40
234 417 2/14/09 Mary 4
500 0
234 417 2/14/09 Mary 5
100 0

Claim 2

Claim # Member ID Date of Serv Prov chline # Amt Req. Amt
pd.
432 417 2/14/09 Mary 1
300 0
432 417 2/14/09 Mary 2
100 0
432 417 2/14/09 Mary 3
800 0
432 417 2/14/09 Mary 4
500 100
432 417 2/14/09 Mary 5
100 75


Claims 1 and 2 aren't duplicates but they meet the criteria for a duplicate
claim. This scenario doesn't happen often, but they are showing up in my
report.

 

[Freida]

Claim 1

Claim # Member ID Date of Serv Prov chline # Amt Req. Amt
pd.
234 417 2/14/09 Mary 1 300 45
234 417 2/14/09 Mary 2 100 5
234 417 2/14/09 Mary 3 800 40
234 417 2/14/09 Mary 4 500 0
234 417 2/14/09 Mary 5 100 0

Claim 2

Claim # Member ID Date of Serv Prov chline # Amt Req. Amt
pd.
432 417 2/14/09 Mary 1 300 0
432 417 2/14/09 Mary 2 100 0
432 417 2/14/09 Mary 3 800 0
432 417 2/14/09 Mary 4 500 100
432 417 2/14/09 Mary 5 100 75


Claims 1 and 2 aren't duplicates but they meet the criteria for a duplicate
claim. This scenario doesn't happen often, but they are showing up in my
report.

 

[John W. Vinson]

Claims 1 and 2 aren't duplicates but they meet the criteria for a duplicate
claim. This scenario doesn't happen often, but they are showing up in my
report.

I guess what I don't understand is: How can Access (or I, or you) TELL that
they aren't duplicates, based on data that exists in the table?

You may need an addtional field, or an additional Claims table (if you don't
inf fact have one).

 

[Freida]

I guess what I don't understand is: How can Access (or I, or you) TELL that
they aren't duplicates, based on data that exists in the table?

You may need an addtional field, or an additional Claims table (if you don't
inf fact have one).

The claims system that we use here is horrible, there is no way to tell if a
claim is being paid twice only if it is possible. I thought there might have
been something that i may have been over looking, but there isn't. I can
only do a report that shows possible duplicates.

Anyway thank you for attempt.

 

[John W. Vinson]

The claims system that we use here is horrible, there is no way to tell if a
claim is being paid twice only if it is possible. I thought there might have
been something that i may have been over looking, but there isn't. I can
only do a report that shows possible duplicates.

Anyway thank you for attempt.

I may be able to help some more... if you'll help me some more. It may not be
possible given the data but, again: how would YOU tell, looking at the data
for these claims, that the claim is a duplicate? What information exists that
lets you make that determination? I'm sure a query can at least help winnow
out the possible duplicates.

Obviously if the claims process is not collecting the information that would
make it possible to identify the duplicates then you're wasting your time...
but is there *anything* in the table that would let you make a reliable
determination?

 

[Bob Barrows]

Claims 1 and 2 aren't duplicates but they meet the criteria for a
duplicate claim.

I think what John is looking for is an explanation of this statement.
What are the criteria for deeming a claim to be a duplicate? What is
telling you that these claims are duplicates?