Query to compare multiple Tables

[Calene]

Need your help please! I have 7 tables which contain Contact information
downloaded from Outlook. We are compiling the list for a mass mailing and
need to check for duplicate entries. Am I able to compare all 7 through one
query or will I need to create multiple queries? Thank you in advance for
your time.

 

[Ben]

If each table have the same fields, for example

table1: name, email
table2: name, email
....
table7: name, email

then just use a union query,

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2
UNION
....
SELECT * FROM TABLE7,

The union will automatically leave out any dups.

Ben

 

[Calene]

Thanks for your quick response Ben. However, I'm not quite sure I follow how
you've laid out the steps below. I've never worked with a Union Query before
so I apologize. Also, I need all 7 tables to be compared against each other
to find the duplicates and I need to see the dups so that I can delete the
records. Any additional assistance you can provide is greatly appreciated.
Thanks!

 

[Ben]

First, check for dups in each table by running the following query over
each of them:

SELECT name, Count(name) AS CountOfName
FROM table1
GROUP BY name;

or use the email field in place of the name field. This query will tell
you how many times each name or email has appeared, then you can open
each table sort the records by name or email and delete the dups based
on the result from the above query. Once you are done deleted the dups,
then u can do the UNION query again.

Ben

 

[John Spencer]

If you want to identify dupes permanently from different tables then one
way is to use a union query that looks like the following (I'm only
using three tables in the exmple):

SELECT [EmailAddress], "TableOne" as TName
FROM TableONe
UNION ALL
SELECT [EmailAddress], "TableTwo"
FROM TableTwo
UNION ALL
SELECT [EmailAddress], "TableTHree"
FROM TableThree

Now with that you can use a find duplicates query
SELECT EmailAddress, TName
FROM qUNION
WHERE EmailAddress IN
(SELECT EmailAddress
FROM qUnion
GROUP BY EmailAddress
HAVING Count(*) > 1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================