[AccessD] Tricky query question

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.
**********************************************************************



More information about the AccessD mailing list