Eliminate duplicate addresses for mailings

[Will]

Access 2007
Vista Business
I have a table with names and home addresses. There are valid duplicate
addresses because of multiple people in the same home. How do I construct a
query to eliminate the duplicate addresses so that I do not send to mailings
to the same address?

Thanks, Will

 

[Jeff Boyce]

Will

So, you have:

John Jones
12345 Elm St
Anywhere, ST 1111

and

Jane Jones
12345 Elm St
Anywhere, ST 1111

Who gets the ONE letter you're going to send?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

Valued Customer of Company X
12345 Elm St
Anywhere, ST 1111

Seriously. I once fixed a system where an insurance company mailed out a
newsletter for each policy. A person could have multiple policies and
multiple people with polices could live at the same house. One address was
receiving 13 copies of the same newsletter each quarter!

But here's the real problem:

John Jones
12345 Elm St
Anywhere, ST 1111
and
Jane Jones
12345 Elm Street
Anywhere, ST 1111-2345

The real fix is an Address table as you have a one to many relationship.

 

[Will]

Does not matter in this case. Just want one mailing to go to each home.
Because the mailings contain no sensative information or information that is
directed to a specific individual it is not worth the effort of splitting
this into two tables and creating the one-to-many relationship.

 

[Jeff Boyce]

Will

It all starts with the data. I'm not sure I fully understand your data
structure, but one way you might approach this is to create one query that
gets everything (including duplicates), then create a second query that
looks only at the addresses and uses the Unique Values property to show one
per unique address.

NOTE: as Jerry points out, someone with a 'plus 4' zipcode WILL be unique
when compared to the standard zip5, so you'll still get what a human would
consider "duplicates" unless you scrub off the 'plus 4' in your first query
so you second query is only comparing standard zip5 values.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Paul Shapiro]

There are commercial address standardization and de-duping programs. They're
based on the post office standard addressing, and include substantial logic
to convert the raw address data into standard form. That greatly reduces the
final number of duplicates. The software tends to be expensive, starting
around $1000 and rising sharply. There are services that will process your
address list for you. It depends on how many addresses you have and how much
you care about eliminating ALL duplicates. Trying to match your raw data is
usually only partly successful. Any variation, no matter how slight,
prevents a match. Until you try it, you'd be surprised how much variation
there is in most address data.

SQL Server's SSIS component has an interesting fuzzy matching tool if you
have access to that. It takes some learning and experimenting but it can
help quite a bit with matching things that are "almost but not exactly" the
same. You can load data from Access into SQL Server for the matching, and
then return your results to Access.

 

[Jeff Boyce]

Excellent leads, Paul! I'm hanging onto these...

Regards

Jeff Boyce
Microsoft Office/Access MVP