[AccessD] First in a group

Drawbridge, Jack: SBMS Drawbridge.Jack at ic.gc.ca
Thu Sep 13 13:49:17 CDT 2007


John,

 "we don't care who gets the mail, just get one piece to
that address"

So how about a query where you Select Distinct on address fields only
(don't include any Person name info), then send a letter to  Dear
Occupant, or Boat Owner or Cat Lover.... as the case may be.
 We've all received Dear Occupant letters

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

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




More information about the AccessD mailing list