[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