[AccessD] First in a group

jwcolby jwcolby at colbyconsulting.com
Thu Sep 13 11:11:25 CDT 2007


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




More information about the AccessD mailing list