Eliminate duplicate addresses for mailings

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

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




"Will" wrote in message
...
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

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Jeff Boyce" wrote:

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

"Will" wrote in message
...
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

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" wrote in message
...
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




"Will" wrote in message
...
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

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


"Will" wrote in message
...
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" wrote in message
...
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




"Will" wrote in message
...
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

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" wrote in message
...
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


"Will" wrote in message
...
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" wrote in message
...
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




"Will" wrote in message
...
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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Paul Shapiro" wrote in message
...
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" wrote in message
...
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


"Will" wrote in message
...
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" wrote in message
...
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




"Will" wrote in message
...
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