rusty.hammond at cpiqpc.com
rusty.hammond at cpiqpc.com
Thu Dec 29 13:59:38 CST 2005
John, As long as your SysName increments along with the PK then picking Min for both the PK and SysName fields will work. If you have a scenario like below (adding to your record list), then picking Min for PK and SysName won't return the correct record info ie: 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) 1750 AHU7138 Air Handling Units returns PK 1750 AHU7135 Air Handling Units 1743 AC0037 Autoclaves (Decon) Notice, the SysName of AHU7135 in the results does not actually belong to PK 1750. Rusty -----Original Message----- From: John Colby [mailto:jwcolby at colbyconsulting.com] Sent: Thursday, December 29, 2005 1:48 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] First of each group That worked perfectly. Thanks Gustav. 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 -- 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. **********************************************************************