Finding duplicates, then selecting files with different values

[JerryT]

I am trying to find duplicates from a table and then limit those records by
selecting lines where my ID field contains different data. For example, I
have dupliacte records from a insurance claims table and I now want to show
claims where a doctor is using two IDs to bill claims.
ID Date Procedure
1 06/29/07 99213
2 06/29/07 99213

After I find the duplicates how do I limit these records by showing only the
duplicate lines where the ID field is different?

 

[John W. Vinson]

I am trying to find duplicates from a table and then limit those records by
selecting lines where my ID field contains different data. For example, I
have dupliacte records from a insurance claims table and I now want to show
claims where a doctor is using two IDs to bill claims.
ID Date Procedure
1 06/29/07 99213
2 06/29/07 99213

After I find the duplicates how do I limit these records by showing only the
duplicate lines where the ID field is different?

Create a Totals query based on the table. Group By [Date] - which is a very
bad fieldname, by the way, since it's a reserved word - and by [Procedure].
Select the ID field into the query *three times*. On one select Count as the
Totals operator, and > 1 as the criterion (to select only those records with
duplicates); on the second use Min as the totals operator, and on the third
use Max. This will show you those records with duplicates and show the two
ID's. If there are triplicates or moreicates, you'll need a different query to
see all the ID's.

I presume that there are more fields involved here, surely - patientID or the
like? I'd expect that having the same procedure run (by a different doctor, on
a different patient) on the same date would be a very common occurance. If so
include these fields in the query and group by them too.

John W. Vinson [MVP]