Compare 3 fields in 2 tables and give differences

access 2003
2 tables

Table1
Table2

3 fields in each table  same field names
fld1 PrimaryKey Dups-ok
fld2 PrimaryKey Dups-ok
fld3 PrimaryKey Dups-ok

What I need:
I need to compare table2 against table1 and tell me what  data exists in table1 that is not table2 ?


Thanks
fordraiders


LVL 3
FordraidersAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


Select A.* From
(
SELECT Table1.fld1, Table1.fld2, Table1.fld3,"Table1" as SourceTable
FROM Table1 LEFT JOIN Table2 ON (Table1.fld3 = Table2.fld3) AND (Table1.fld2 = Table2.fld2) AND (Table1.fld1 = Table2.fld1)
WHERE (((Table2.fld1) Is Null) AND ((Table2.fld2) Is Null) AND ((Table2.fld3) Is Null))

Union All
SELECT Table2.fld1, Table2.fld2, Table2.fld3, "table2"
FROM Table2 LEFT JOIN Table1 ON (Table2.fld3 = Table1.fld3) AND (Table2.fld2 = Table1.fld2) AND (Table2.fld1 = Table1.fld1)
WHERE (((Table1.fld1) Is Null) AND ((Table1.fld2) Is Null) AND ((Table1.fld3) Is Null))
) As A
0
 
Dale FyeCommented:
Try:

Select T1.fld1, T1.fld2, T1.fld3
FORM Table1 as T1
LEFT JOIN Table2 as T2
ON T1.fld1 = T2.fld1
and T1.fld2 = T2.fld2
AND t1.fld3 = t2fld3
WHERE T2.fld1 IS NULL
0
 
FordraidersAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.