SQL to identify duplicate records in to tables

[Guest]

Hello, I'm using Access 2002

I have two tables in the same Access DB. I need a SQL string that will somehow identify those records in Table1 that also reside in Table2.

This could be done by marking the duplicate rows in a new column in Table1 or by creating a new table in which the duplicates are copied, or whatever

A duplicate row can be identified by comparing 3 of the 18 columns in each table (call them Field1, Field2, Field3)

1. Can this be done with SQL

2. If so, could someone please post example SQL I can adapt to my needs

3. If SQL can't be used, how can I do this

Thanks much in advance.

 

[Gerald Stanley]

Try something along the lines of the following untested air
code to make a duplicates table
SELECT Table1.* INTO DuplicateTable
FROM Table1 INNER JOIN Table2 ON (Table1.field3 =
Table2.field3) AND (Table1.field2 = Table2.field2) AND
(Table1.field1 = Table2.field1);

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Hello, I'm using Access 2002.

I have two tables in the same Access DB. I need a SQL

string that will somehow identify those records in Table1
that also reside in Table2.

This could be done by marking the duplicate rows in a new

column in Table1 or by creating a new table in which the
duplicates are copied, or whatever.

A duplicate row can be identified by comparing 3 of the 18

columns in each table (call them Field1, Field2, Field3).

 

[Gerald Stanley]

It depends what you are going to do with the 'duplicate'
table. If you are looking for those combinations of the
three columns that appear together in both tables, you
could use
SELECT DISTINCT Table1.field1, Table1.field2, Table3.field3
INTO DuplicateTable
FROM Table1 INNER JOIN Table2 ON (Table1.field3 =
Table2.field3) AND (Table1.field2 = Table2.field2) AND
(Table1.field1 = Table2.field1);

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Stanley,

Thanks for the SQL. It runs great. The only thing is, it

returns more rows than are in my smaller table (Table1),
which indicates I guess that there is more than one match
in the larger table (Table2)?

Is there any way the SQL can be tweaked to only match

once? Or should I somehow work out an update on a column in
the smaller table (Table1) to show the matches? If so, how
do I do this?