Difference between to table and union


E

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)
 
Ad

Advertisements

J

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
'====================================================
 
Ad

Advertisements

K

KARL DEWEY

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top