Compare Multiple Records

[betachristopher]

I need to create a query that compares multiple records in the same table. Below are examples of the data I am looking to compare. In the first list, you can see that one of the records has a different DOB. In the second list, you can see that one of the PATIENT numbers is different.

I want my query to show the records with incorrect data as well as the related records with the correct data. What is the best way to create this query?

SITE PATIENT DOB ACCN1 ACCN2 VISIT_DSCR
103942 1039427004 11/13/1948 AB-A-364 AB00475 Visit 1 (Week-4)
103942 1039427004 11/13/1948 AB-B-364 AB00475 Visit 1 (Week-4)
103942 1039427004 11/11/1948 AB-P-364 AB00477 Visit 1 (Week-4)
103942 1039427004 11/13/1948 AB-A-370 AB01589 Unscheduled
103942 1039427004 11/13/1948 AB-B-370 AB01589 Unscheduled

SITE PATIENT DOB ACCN1 ACCN2 VISIT_DSCR
103952 1039527048 10/22/1955 AB-A-991 AB02337 Visit 1 (Week-4)
103952 1039527048 10/22/1955 AB-B-991 AB02337 Visit 1 (Week-4)
103952 1039527048 10/22/1955 AB-P-991 AB02337 Visit 1 (Week-4)
103952 1035927048 10/22/1955 AB-P-2052 AB02340 Early Termination

Using Access 2003 with a SQL back end.

 

[jedraw]

How do you know which value is correct?
Does your database have multiple tables?
One for PATIENT Details and another for Visits?
Your Patient info should be traceable back to Patient (number). Your table relationships should prevent a site visit for a non-existing Patient number.

Tell us more about your database and tables.

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

 

[SkipVought]

How were these TWO results generated? If a query, please post the SQL.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[betachristopher]

Here is the SQL query. There's nothing in the database to indicate which data is incorrect, which is why I would like the query to return all related records so the user can determine which is correct. I'm considering the three identifiers to be SITE, PATIENT AND DOB. If records exist with at least two matching identifiers, but the a third identifier doesn't match, I want the query to return all of those records.

Thanks PHV. I will check out this link.

SELECT PATIENT.SITE, PATIENT.PATALLOC AS PATIENT, PATIENT.DOB, PATIENT.ACCN1, PATIENT.ACCN2, PATIENT.VISIT_DSCR
FROM PATIENT
WHERE PATIENT.STUDY="AB"
ORDER BY PATIENT.SITE, PATIENT.PATALLOC;

 

[SkipVought]

You have hosed up data!

Looks as if someone did a cartesian join UPDATE at some point in time.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]