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.Employ eeID AS ID,
Employee.Surnam e AS FullName FROM Employee
UNION ALL SELECT "Client" AS TableName,
tClient.ClientN um,
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.TableNam e,
Query9.ID,
Query9.FullName
FROM Query10 INNER JOIN Query9 ON Query10.FullNam e = 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*******@char ter.net> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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?
