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