Difference between to table and union

[ecwhite]

Hello Please Help,

I have two tables table1, and table2. They both have patient information
with patient_id as unique identifier.

Table1 has 26609 patients and table two has 26638 patients which is the same
record except table2 has more patients than table1.

I just want to get the records (patients) that are not in both tables. I
already have a union query that combines them together e.g

SELECT table1.[patient_id] FROM table1;

UNION ALL SELECT table2.[patient_id] FROM table2;

For difference I have tried

Where (isnull(table1) = False AND isnull(table2) = True)
OR (isnull(table1) = True AND isnull(table2) = False)
OR (table1 <> table2)

 

[John Spencer]

Use the unmatched query wizard to build a query that returns all records
in table1 that are not in Table2.

Use the umnatched query wizard again to return all records in table2
that are not in table1

Join the query results with a UNION query. You might want to end up
with a query that looks like the following.

SELECT Table1.Patient_ID
, "Only Table1" as fOnly
FROM Table1 LEFT JOIN Table2
ON Table1.Patient_ID = Table2.Patient_ID
WHERE Table2.Patient_ID is not Null

UNION ALL

SELECT Table2.Patient_ID
, "Only Table2" as fOnly
FROM Table1 RIGHT JOIN Table2
ON Table1.Patient_ID = Table2.Patient_ID
WHERE Table1.Patient_ID is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

SELECT table2.[patient_id]
FROM table2 LEFT JOIN ON table2.[patient_id]= table1.[patient_id]
WHERE table1.[patient_id] Is Null ;