Comparing data between 2 tables

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have two seperate tables and wondered if there is a way I could check to
see if there are any records where 3 of the fields are duplicated with an
"or" statment and an "and" statement:

eg where field 1 from table 1 matches field 1 from table 2 OR field 2 from
table 1 matches field 2 from table 2 AND field 3 from table 1 matches field 3
from table 2 .

I'm not sure how I would create a relationship between the tables given that
the if I match on any one field it will presumably cloud the issue of
duplicates on the other fields?

Please note I am not a VBA expert!
Thanks
Tony
 
Thanks Gordon I'll play around with that. Presumably I don't need to link the
tables in Query Design just type the statement in the sql view?
 
Hi Tony,

This will only return records from table1 where all 3 fields are
duplicated -

SELECT * FROM table1 WHERE ((table1.field1=table2.field1) AND
((table1.field2=table2.field2) AND (table1.field3=table2.field3)));

The AND means both sides have to be true - the OR means one of the sides
needs to be true and the other can be false.

This will return records from table1 where any single one of the fields are
duplicated -

SELECT * FROM table1 WHERE ((table1.field1=table2.field1) OR
((table1.field2=table2.field2) OR (table1.field3=table2.field3)));

Hope this helps,
Gordon
 
That is not a VBA problem. It is an SQL problem. The SQL statement for
this would look like the following - this type of query can only be
entered in SQL View.

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1
OR (Table1.Field2 = Table2.Field2
AND Table1.Field3 = Table2.Field3)

Another way to do this using the query design view is
-- Add both tables to the query with no join line
-- Add the fields you want to see
-- Under Table1.field1 enter the following on the FIRST criteria line
=[Table2].[Field1]
-- Under Table1.Field2 enter the following on the 2nd criteria line
=[Table2].[Field2]
-- Under Table1.Field3 enter the following on the 2nd criteria line
=[Table2].[Field3]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks John that looks relatively simple I'll try that out and get back if I
have any problems
Tony

John Spencer said:
That is not a VBA problem. It is an SQL problem. The SQL statement for
this would look like the following - this type of query can only be
entered in SQL View.

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1
OR (Table1.Field2 = Table2.Field2
AND Table1.Field3 = Table2.Field3)

Another way to do this using the query design view is
-- Add both tables to the query with no join line
-- Add the fields you want to see
-- Under Table1.field1 enter the following on the FIRST criteria line
=[Table2].[Field1]
-- Under Table1.Field2 enter the following on the 2nd criteria line
=[Table2].[Field2]
-- Under Table1.Field3 enter the following on the 2nd criteria line
=[Table2].[Field3]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Tony said:
I have two seperate tables and wondered if there is a way I could check to
see if there are any records where 3 of the fields are duplicated with an
"or" statment and an "and" statement:

eg where field 1 from table 1 matches field 1 from table 2 OR field 2 from
table 1 matches field 2 from table 2 AND field 3 from table 1 matches field 3
from table 2 .

I'm not sure how I would create a relationship between the tables given that
the if I match on any one field it will presumably cloud the issue of
duplicates on the other fields?

Please note I am not a VBA expert!
Thanks
Tony
Click to expand...
Click to expand...
 
Back
Top