Make one final list from four sources

[magmike]

Have 4 different Excel spreadsheets from four sources and need to generate one contact list from those four sources that is void of duplicates based on email address.

What is the best strategy to do this? I am a novice access user with not enough expereince to be efficient in how I generate a result.

My best thought is to import each source as a table, generate 4 different append queries that add them all to a fifth table, then a query that onnly shows unique values. Is there a more efficient way?

Thanks in advance for your help,
magmike

 

[John W. Vinson]

Have 4 different Excel spreadsheets from four sources and need to generate one contact list from those four sources that is void of duplicates based on email address.

What is the best strategy to do this? I am a novice access user with not enough expereince to be efficient in how I generate a result.

My best thought is to import each source as a table, generate 4 different append queries that add them all to a fifth table, then a query that onnly shows unique values. Is there a more efficient way?

Thanks in advance for your help,
magmike

Yes, though it's a bit obscure.

You can use the "External Data" button to link to - not import from - the four
spreadsheets. Let's call them Sheet1, Sheet2, Sheet3 and Sheet4 - user your
own names of course!

Then create a UNION query. You have to do this in the SQL window; the grid
isn't smart enough to handle it. Assuming that all four sheets have the same
column headers, you could just use

SELECT Sheet1.*
UNION
SELECT Sheet2.*
UNION
SELECT Sheet3.*
UNION
SELECT Sheet4.*

This query will eliminate all EXACT duplicates - you might still have records
where the email is the same but the names are spelled differently, or one
person is at 324 Main St. and another instance of the same person at 324 Main
Street. You can get it down to one record per person by email by creating a
TOTALS query based on the UNION query.

If you don't care which instance of multiple records with the same email you
see, just GROUP BY the email address and choose the FIRST totals option on the
other fields.

If it DOES matter, you may indeed need to append into a table, and manually or
programmatically select which ones to keep.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com