[AccessD] First of each group

I thought this would return ONE record.  I need ONE record for each
SysDescr.  There are perhaps 30 systems, but 1 to 10 Instances of each
system.  Thus there are hundreds of records in the system table.

I need to get ONE INSTANCE of each system, the instance with the lowest
(first) PK.   


John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, December 29, 2005 1:36 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] First of each group

Hi John

Seems like you need to Group By SysDescr and pick Min for the other fields
... or is this not a full example?

/gustav

>>> jwcolby at ColbyConsulting.com 29-12-2005 19:23:43 >>>
I have a "system" table, with the PKID (autonumber), the name of the system,
and the system description:

PK	Sys Name	Sys Descr
1752	AHU7135	Air Handling Units
1754	AHU7137	Air Handling Units
1743	AC0037	Autoclaves (Decon)
1744	AC0038	Autoclaves (Decon)
1745	AC0039	Autoclaves (Decon)

Etc.  

I need to select the PK, Name and description for the FIRST instance of each
system of a given description, and populate a combo with the results:

1752	AHU7135	Air Handling Units
1743	AC0037	Autoclaves (Decon)

I don't know how to do this is a simple SQL statement.  Any suggestions?

John W. Colby
www.ColbyConsulting.com 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com