[AccessD] First of each group

rusty.hammond at cpiqpc.com rusty.hammond at cpiqpc.com
Thu Dec 29 13:45:53 CST 2005


John,

How about a subquery like the following (assuming your table name is Table1)

SELECT SysDescr, PK, SysName 
FROM Table1
WHERE PK IN 
(SELECT Min(Table1.PK) AS MinOfPK
FROM Table1
GROUP BY Table1.SysDescr);

HTH

Rusty Hammond

-----Original Message-----
From: John Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Thursday, December 29, 2005 1:41 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] First of each group


Yes, but I need to display this in a combo so that a system can be selected,
the PK of which will be used to select a document further down the line.


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 Jim DeMarco
Sent: Thursday, December 29, 2005 1:41 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] First of each group

Don't know about SQL but you can do with a repoort.  Get the data into a
query and base a report on it sorted by system name.  In the report but a
group header on system name and set Group On property to Prefix characters.
Set character number to 3.  Add another sort by system name here if
necessary.  Put all output fields in the header and hide the detail section.

Should show you the top item in each group.

HTH

Jim DeMarco

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, December 29, 2005 1:24 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] First of each group


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 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

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


****************************************************************************
*******
"This electronic message is intended to be for the use only of the named
recipient, and may contain information from Hudson Health Plan (HHP) that is
confidential or privileged.  If you are not the intended recipient, you are
hereby notified that any disclosure, copying, distribution or use of the
contents of this message is strictly prohibited.  If you have received this
message in error or are not the named recipient, please notify us
immediately, either by contacting the sender at the electronic mail address
noted above or calling HHP at (914) 631-1611. If you are not the intended
recipient, please do not forward this email to anyone, and delete and
destroy all copies of this message.  Thank You".
****************************************************************************
*******

-- 
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

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************



More information about the AccessD mailing list