David McAfee
davidmcafee at gmail.com
Thu Jun 14 14:13:48 CDT 2012
Sorry, I didn't read that correctly. This should work: SELECT B.ClientID, B.EmpID, B.RecDate FROM ( SELECT ClientID, Max(MinDate) AS MaxDate FROM (SELECT ClientID, EmpID, Min(RecDate) AS MinDate FROM SomeTable GROUP BY ClientID, EmpID) C GROUP BY ClientID )A INNER JOIN SomeTable B ON A.ClientID = B.ClientID AND B.RecDate = A.MaxDate On Thu, Jun 14, 2012 at 11:27 AM, Rusty Hammond <rusty.hammond at cpiqpc.com>wrote: > 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. > ********************************************************************** > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >