Dedupe mailling list

[Guest]

Hi
I have a table of 179000 records - but there are only 140000 uniques. I have
a field which is the number of the house and the postcode (eg 20RG11RG).
This means that each duplicate has the same in this field - regardless of
whether the name is Mr Smith, Mrs Smith, J Smith etc. I can use a query,
with unique values set to Y, to get a list of this field - and hence a
quantity. How do I now link this to the existing table to get a full list of
the names and addresses?
As an example, I might have
Mr Smith, 21 Acacia Avenue, RG1 1RG, 21RG11RG
Mrs Smith, 21 Acacia Ave, RG1 1RG, 21RG11RG
In my query, I just have one entry for RG1 1RG. How do I now add a name and
address to it? (either Mr or Mrs Smith).
Thanks!

 

[John Spencer]

If you don't care which names you get, you could always use a Totals query.

Using the query grid
Set up a query to return ALL the fields you want in your final result.
Select View:Totals
In the totals line change GROUP BY to FIRST for all the fields except the
field that contains the house and postcode - leave that as Group by

See if that gives you the desired result.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Yep!! That'll do for me!! Thanks very much. I was working on the theory that
if I started with a unique list I could do it from there!

Thanks again.
Andy.