>>__________
>>>No, it's not enough to de-dup on SSN + dDOB. We found cases there different patients have the same SSN & dDOB (perhaps, Identity theft cases, but it's not our job to report them). That's why I want to use a complex query. Also we may have situation when we changed address or added phone for patients. I want to preserve these records, even if it means we would not have them all properly de-duped. So, I want to save as much info as possible and de-dup based on the complex criteria rather than just SSN and DOB.
>>___________
>>
>>OK, I surrender
>
>After trying various combunations of queries, I think, you're right.
>
>I first have to do something like
>
>select cPatients_fk, tEntered_Date, ;
>tLast_Updated_Date from Patients ;
>inner join ;
>(select count(*) as cntDups, Patients.dDOB, Patients.cSSN ;
>from Patients group by 2,3 having cntDups >1) step1 ;
>on Patients.dDOB = step1.dDOB and Patients.cSSN = step1.cSSN
>
>Then I'll join with the rest of the tables and then it would be clearer what to eliminate and what to keep.
I would start with [ cSSN, count(*) having count(*) > 1 into cursor Step_1 ]
That will give you the possible problem areas. You only have to consider those and refine
Gregory