Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Compare 3 fields in 2 tables and give differences

Posted on 2011-09-21
Medium Priority
307 Views
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

0
Question by:Fordraiders

LVL 49

Expert Comment

ID: 36574517
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

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36574566

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

LVL 3

Author Closing Comment

ID: 36577279
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
###### Suggested Courses
Course of the Month15 days, 4 hours left to enroll