By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Find duplicate records in multiple tables

P: n/a
I'm trying to find a way to search multiple tables for the same record.
Say I have 3 tables all with a name column, I need to search all 3
tables and find matching names. Is there an easy way to accomplish this?

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can do this by stacking queries on top of each other.

1. Create a UNION query that keeps track of the table name and primary key
value for each record. This example shows how to do that with 2 tables, but
you can add more with another UNION. The SQL statement will look like this:
SELECT "Employee" AS TableName,
Employee.EmployeeID AS ID,
Employee.Surname AS FullName FROM Employee
UNION ALL SELECT "Client" AS TableName,
tClient.ClientNum,
tClient.Surname FROM tClient;
Save with the name "Query9".

2. Create another query that uses the first one as in input table. This one
returns only the names that are duplicated, and tells how many there are:
SELECT Query9.FullName,
Count(Query9.ID) AS CountOfID
FROM Query9
GROUP BY Query9.FullName
HAVING (((Count(Query9.ID))>1));
Save with the name "Query10".

3. Create another query that shows you the duplicated records, including the
name of the table, the primary key value, and the duplicated name:
SELECT Query9.TableName,
Query9.ID,
Query9.FullName
FROM Query10 INNER JOIN Query9 ON Query10.FullName = Query9.FullName;

If your situation is actually more complex, so that more than 1 field
defines the duplicate, you can included these other fields also in the
original UNION query and the GROUP BY of the middle query, and the JOIN of
the final query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian" <br*******@charter.net> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I'm trying to find a way to search multiple tables for the same record.
Say I have 3 tables all with a name column, I need to search all 3
tables and find matching names. Is there an easy way to accomplish this?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.