473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Find duplicate records in multiple tables

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
1 10544
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: steevp | last post by:
Hi, Please excuse the newbie type question, but I am wracking my brains to solve what should be a simple query. I have three fields in a table F1, F2, F3. Any of the fields may contain...
1
by: Jameel | last post by:
how do i find duplicate records in ASP.net/ADO.net , i know how to do it in classic asp, anything appreciated code,tutorial links etc.. (if rs.eof then add record else error message end if )
2
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to...
3
GrandMaster
by: GrandMaster | last post by:
Hi all I'm using MS Access 97 and in my current database (a flat single table database) I have a field for FirstName and one for FamilyName. I've been given two different lots of data to import...
2
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT...
5
by: Sheol | last post by:
Hello everyone! Can somebody please help me with this... I have a VB program that inputs Name and Address. I've made a database in MS Access using ADO Control: Table Name: Contacts Fields are:...
1
by: colin-whitehead | last post by:
I have 2 tables tblReports primary key UPN, plus numeric fields Effort, Attain, etc tblComments numeric primary key ID & textfield Text In the Query I select each record from tblReports...
4
dlite922
by: dlite922 | last post by:
Short Version How do you find the difference between two database with the same structure but different data? with SQL (results will be ingested with PHP). Long Version Seems like a little...
1
by: anantha raman | last post by:
Hai, I am a beginer in Developing ASP.NET webpage. I want to restrict duplicates while uploading the excel data and to inform the user that the perticular cell in excel contains duplicate value...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.