[AccessD] First in a group

jwcolby jwcolby at colbyconsulting.com
Fri Sep 14 09:23:01 CDT 2007


Yep.  And the reality is that we don't know who is who, or what their
relationship is to anyone else in the house.  I have first, last and
address.  Beyond that not a clue about the relationships. 


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 Perry L Harold
Sent: Friday, September 14, 2007 9:56 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] First in a group

In direct mail the first job is to get the mailing piece opened.  Having a
name means it's less likely to be deposited in the round file without even
being opened. 


Perry Harold


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, September 13, 2007 3:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] First in a group

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

--
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




More information about the AccessD mailing list