Eliminate duplicate addresses for mailings
in [Queries]
|
Prev: Parse Name
Next: Changing Criteria in Access Queries
From: Will on 25 Jun 2008 11:04 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
From: Jeff Boyce on 25 Jun 2008 11:08 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" <will(a)k2e.com> wrote in message news:%238hbbTt1IHA.1236(a)TK2MSFTNGP02.phx.gbl... > 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
From: Jerry Whittle on 25 Jun 2008 11:55 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" <will(a)k2e.com> wrote in message > news:%238hbbTt1IHA.1236(a)TK2MSFTNGP02.phx.gbl... > > 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
From: Will on 25 Jun 2008 13:06 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" <nonsense(a)nonsense.com> wrote in message news:eB$EnVt1IHA.4476(a)TK2MSFTNGP06.phx.gbl... > 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" <will(a)k2e.com> wrote in message > news:%238hbbTt1IHA.1236(a)TK2MSFTNGP02.phx.gbl... >> 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 > > >
From: Jeff Boyce on 25 Jun 2008 14:23
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" <will(a)k2e.com> wrote in message news:495C5090-441E-487B-A7AC-0192248B2507(a)microsoft.com... > 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" <nonsense(a)nonsense.com> wrote in message > news:eB$EnVt1IHA.4476(a)TK2MSFTNGP06.phx.gbl... >> 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" <will(a)k2e.com> wrote in message >> news:%238hbbTt1IHA.1236(a)TK2MSFTNGP02.phx.gbl... >>> 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 >> >> >> > |