Rusty Hammond
rusty.hammond at cpiqpc.com
Thu Jun 14 13:27:45 CDT 2012
Correct, I don't want the largest date for the client, I want the last time the EmpID changed for that client. Rusty -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Thursday, June 14, 2012 1:16 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Tricky query question Won't that select the row for each clientID that has the largest date? i.e. 1111 5678 4/25/2012 2222 4567 1/1/2012 which was not the desired result -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee Sent: Thursday, June 14, 2012 1:15 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Tricky query question SELECT B.ClientID, B.EmpID, B.RecDate FROM (SELECT ClientID, Max(RecDate) AS MaxDate FROM SomeTable GROUP BY ClientID) A INNER JOIN SomeTable ON A.ClientID = B.ClientID On Thu, Jun 14, 2012 at 10:02 AM, Rusty Hammond <rusty.hammond at cpiqpc.com>wrote: > I have a table of historical data for our clients. The data consists > of the ClientID, the ID for the employee responsible for that client > and a date pertaining to the record. I need to get the latest date > the responsible party changes. For example with the following dataset: > > ClientID EmpID RecDate > 1111 5498 1/1/2009 > 1111 1234 1/1/2010 > 1111 5678 2/15/2012 > 1111 5678 4/25/2012 > 2222 1234 6/5/2006 > 2222 6789 5/1/2007 > 2222 4567 9/26/2011 > 2222 4567 1/1/2012 > > I want the results > > ClientID EmpID RecDate > 1111 5678 2/15/2012 > 2222 4567 9/26/2011 > > I can spin through the recordset in code and do this, but can I do > this with a query or queries? > > Thanks, > > Rusty Hammond > CPI Qualified Plan Consultants, Inc. > rusty.hammond at cpiqpc.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 > -- 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. **********************************************************************