Mailing List Query

[Matt Cook]

I am using Access 97 on a PC running Win 2K. I have a
voter list, about 6000 records. I want to create a query
that will find the duplicates, ie 2 people living at the
same address and then move the duplicates to a different
area so that I can create mailing labels that will have
both peoples names. I am new to Access so am help would be
appreciated. Thanks!

 

[Jeff Boyce]

Matt

Are you saying that your data can have more than one person using the same
address? If so, finding the "duplicate use" of an address will depend on
several factors.

First, do you actually have the addresses physically typed into fields, or
do you have an address table and only use the key from that table in your
"this person at this address" determination?

Next, if you do have the addresses re-entered for each person, can you
absolutely-positively state, without concern, that all addresses have been
correctly entered without typos, or variations ... for example, do you
consider the following to be "duplicate addresses"?

12345 Elm Street

12345 Elm St

12354 Elm St

12345 Elm St SW

Good luck!

Jeff Boyce
<Access MVP>

 

[Matt Cook]

Jeff,

The fields were imported from an excel spreadsheet, and
all of the addresses are entered in the same format, ie
1234 Elm Street. I used the query wizard to find the
duplicates, and that worked. Now I need to find a way to
seperate the duplicates so I can print mail labels that
will read "John and Joan Q. Public 1234 Elm Street". To do
that I need a way to combine the "First Name" field for a
duplicate address?

 

[Jeff Boyce]

Matt

OK, are you ONLY looking for duplicates, or is your task to create mailing
labels, and to handle duplicates in that process?

If so, one approach might be to build an initial query that returns one
instance of every unique address (a query of addresses, with UniqueValue
property set to 'Yes'). Using that query as a starting point, build a
second query that joins the unique addresses back to the address field in
your imported address table. Sort by address. All your duplicate addresses
will end up with more than one row (same address, different name), while all
the unique addresses (only one person) will have only one row.

Now, you'll have to build a function in a code module that iterates through
that second query and builds "output" for labels. If the code finds a
second address row with the same address, it concatenates the name to the
previous row's. If it finds a new address, it creates a new label.

Good luck!

Jeff Boyce
<Access MVP>