jwcolby
jwcolby at colbyconsulting.com
Thu Sep 13 14:05:03 CDT 2007
LOL. Sounds like a plan. Unfortunately the advertisers prefer names. And I just do what I am asked. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos Sent: Thursday, September 13, 2007 2:52 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] First in a group How about if you have multiple names at and address then addresing to "Boat Owner" rather than a name. Or maybe "Rich Guy Boat Owner" ;-) GK On 9/13/07, jwcolby <jwcolby at colbyconsulting.com> wrote: > The problem here is that with mailing lists, particularly where you > have 50 million names in the list, you haven't a clue who is who, just > that they share the same last name and live at the same location. > > In the case I am working on now, they want males, making BIG bucks, > who have a boat. I am seeing three males (all different names) all > apparently claiming the boat, and all claiming to make big bucks. The > client only wants to send one piece of mail to the address. > > Who do YOU choose? > > As you can see, there are often criteria (only target the males) etc. > We do have age fields, sometimes they are filled in, sometimes the > client wants to target an age group. In those cases the age group > narrows who gets the mail. Not so in this specific case. > > In a previous mailing, they wanted women who owned a cat, in specific > income groups. Then they wanted Women, who owned a dog, in specific income groups. > Neither could be in the other mailing (dog owners could not get the > cat mailer, cat owners could not get the dog mailer). What a PITA THAT was. > > But yea, in general, "we don't care who gets the mail, just get one > piece to that address". > > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim > Sent: Thursday, September 13, 2007 12:46 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] First in a group > > > So if you have parent/children or parent/grandparents living at the > same address it is pot luck as to who gets the mailing? The child or > grandparent or inlaw for that matter might get a letter intended for > the parent? That could be interesting! :-) Jim Hale > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, September 13, 2007 11:11 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] First in a group > > Thanks all who replied. > > I added an autonumber ID to the table. > > 1) Extracted all matching records to a temp table. > 2) Added an autonumber ID so I could uniquely point to any given record. > 3) Created qFirstID where I Grouped By the address hash and pulled the > First(ID) > > That gave me the correct subset of records where there is only one ID > for each address. > > 4) Joined that ID from qFirstID with tblOrder on the ID in > qOrderFirstID > 5) Selected all the name / address fields and viewed them to ensure no > dupes. NO DUPES. > > 6) Used that to export the data > > Again thanks for the suggestions. I really have to write up a > document of how I do this stuff!!! > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte > Foust > Sent: Thursday, September 13, 2007 11:07 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] First in a group > > John, > > Group on the address and use First([person]) to get the nanme. > > Charlotte Foust > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Thursday, September 13, 2007 7:06 AM > To: 'Access Developers discussion and problem solving' > Subject: [AccessD] First in a group > > I have a set of address records where there are several people at the > address. I need to send only one piece of mail to that address, the > person doesn't matter. > > John Colby, Some Street, SomeCity, SomeState, SomeZip Mary Colby, Some > Street, SomeCity, SomeState, SomeZip John Doe, Some Other Street, Some > Other City, Some Other State, Some Other Zip Mary Doe, Some Other > Street, Some Other City, Some Other State, Some Other Zip > > I don't care whether I send to Mary or John, I only care that only a > single piece of mail goes to each address. > > Select distinct of course acts on the entire row and so returns all 4 names. > > I do have an address hash that generates a hash string for just the > address part. > > So it seems like something like a group by address hash and then a > top1 is the right direction, but of course it has to be the TOP 1 in each GROUP. > > > I could (and might have to to get this out) write a little VBA routine > to do this but I would rather use a SQL solution. > > So how is this done in SQL. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > ********************************************************************** > * The information transmitted is intended solely for the individual or > entity to which it is addressed and may contain confidential and/or > privileged material. Any review, retransmission, dissemination or > other use of or taking action in reliance upon this information by > persons or entities other than the intended recipient is prohibited. > If you have received this email in error please contact the sender and > delete the material from any computer. As a recipient of this email, > you are responsible for screening its contents and the contents of any > attachments for the presence of viruses. No liability is accepted for > any damages caused by any virus transmitted by this email. > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com