[AccessD] First in a group

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



More information about the AccessD mailing list