Duplicates Query

A

Anna

I am working with Access 2003 - I have two client databases and I want to
compare both lists, find out what clients are on one list and not the other.

My question is what and how do I create a query that will sort through both
lists and come back to me with names that do not match? For example, on one
list there are 854 clients, the other list has 900. I would like to run a
query which will show me the 46 clients not on the 854 list.

Hope I haven't confused anyone and hope this is possible to do. Thanks!!

Anna
 
T

TedMi

It depends by what you mean by matching names. Are McDonald, MacDonald and
MacDonald matches or not? And can you be sure that the shorter list is a
proper subset of the longer one? That is, are all names on the shorter also
on the longer? But if the longer list was created by adding entries to a copy
of the shorter, and no changes have been made to the names in common, then
this will do what you want

SELECT * FROM LongList
WHERE LongList.LastName & LongList.FirstName NOT IN
(SELECT SHortList.LastName & ShortList.FirstName FROM ShortList)

CAUTION: Air code - Not tested!
 
S

Smartin

Anna said:
I am working with Access 2003 - I have two client databases and I want to
compare both lists, find out what clients are on one list and not the other.

My question is what and how do I create a query that will sort through both
lists and come back to me with names that do not match? For example, on one
list there are 854 clients, the other list has 900. I would like to run a
query which will show me the 46 clients not on the 854 list.

Hope I haven't confused anyone and hope this is possible to do. Thanks!!

Anna
Click to expand...

Have you tried the Unmatched Query Wizard?
 
A

Anna

Hi - I have two lists which are suppose to be identical except 46 clients
were not placed on the shorter list. I wanted to see if there was a query
which would compare both lists and only bring out the 46 from the bigger list
so I can add them is the smaller one so both will match once again.
 
Top