SQL to identify duplicate records in to tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
columns in each table (call them Field1, Field2, Field3).
 
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
Click to expand...
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
Click to expand...
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?
 
Back
Top