If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
#3
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
#4
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
#5
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
#6
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
#7
|
|||
|
|||
Eliminate duplicate addresses for mailings
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 |
Thread Tools | |
Display Modes | |
|
|