Gary Kjos
garykjos at gmail.com
Thu Sep 13 13:51:30 CDT 2007
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