[AccessD] First in a group

John,

  Do a group by on the combined Street, City, State, and Zip.  Then select
FIRST or LAST for all other fields.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, September 13, 2007 10: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