[AccessD] Tricky query question

Rusty Hammond rusty.hammond at cpiqpc.com
Thu Jun 14 14:48:28 CDT 2012


Looks like that's going to work.

Thank you David!

Rusty 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Thursday, June 14, 2012 2:14 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Tricky query question

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