John Colby
jwcolby at ColbyConsulting.com
Thu Dec 29 14:13:37 CST 2005
Oh... That is not good. I have no idea whether the system number is in step with the autonumber PK, although I think it does. The source data was a spreadsheet with the systems arranged in order across the sheet. Perhaps I could remove the min from the sysname and put in a bogus where - <> "" or something. 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 rusty.hammond at cpiqpc.com Sent: Thursday, December 29, 2005 3:00 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] First of each group 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. ********************************************************************** -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com