Re: [MS_AccessPros] Re: Query the most recent date!

Rohn-

When you reply on the web, be sure to click Show Message History at the bottom of the reply window to make sure the thread stays intact.

The SQL I gave you does find the latest by employee ID:

SELECT License_Cert.[Emp#], License_Cert.Cert_Date, License_Cert.Cert_Title, License_Cert.Cert_NO, License_Cert.Issuing_Authority, Year([Cert_Date]) AS [YEAR], [First_Name] & ' ' & [Last_Name] AS Name, License_Cert.Cert_Expire_Date
FROM License_Cert INNER JOIN dbo_EMPLOYEE ON License_Cert.[EMP#] = dbo_EMPLOYEE.ID
WHERE (((License_Cert.[Emp#]) Like "*" & [Enter Employee Number:] & "*") AND ((License_Cert.Cert_Title) Like "*" & [Enter Cert Title:] & "*"))
AND License_Cert.Cert_Expire_Date = (SELECT MAX(Cert_Expire_Date) FROM License_Cert As L2 WHERE L2.[EMP#] = dbo_EMPLOYEE.ID);

If you want the latest by employee ID AND certificate, then do:

SELECT License_Cert.[Emp#], License_Cert.Cert_Date, License_Cert.Cert_Title, License_Cert.Cert_NO, License_Cert.Issuing_Authority, Year([Cert_Date]) AS [YEAR], [First_Name] & ' ' & [Last_Name] AS Name, License_Cert.Cert_Expire_Date
FROM License_Cert INNER JOIN dbo_EMPLOYEE ON License_Cert.[EMP#] = dbo_EMPLOYEE.ID
WHERE (((License_Cert.[Emp#]) Like "*" & [Enter Employee Number:] & "*") AND ((License_Cert.Cert_Title) Like "*" & [Enter Cert Title:] & "*"))
AND License_Cert.Cert_Expire_Date = (SELECT MAX(Cert_Expire_Date) FROM License_Cert As L2 WHERE L2.[EMP#] = dbo_EMPLOYEE.ID
AND L2.Cert_NO = License_Cert.Cert_NO);

If the certification numbers aren't unique, then you'll have to do it on title:

SELECT License_Cert.[Emp#], License_Cert.Cert_Date, License_Cert.Cert_Title, License_Cert.Cert_NO, License_Cert.Issuing_Authority, Year([Cert_Date]) AS [YEAR], [First_Name] & ' ' & [Last_Name] AS Name, License_Cert.Cert_Expire_Date
FROM License_Cert INNER JOIN dbo_EMPLOYEE ON License_Cert.[EMP#] = dbo_EMPLOYEE.ID
WHERE (((License_Cert.[Emp#]) Like "*" & [Enter Employee Number:] & "*") AND ((License_Cert.Cert_Title) Like "*" & [Enter Cert Title:] & "*"))
AND License_Cert.Cert_Expire_Date = (SELECT MAX(Cert_Expire_Date) FROM License_Cert As L2 WHERE L2.[EMP#] = dbo_EMPLOYEE.ID
AND L2.Cert_Title = License_Cert.Cert_Title);

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)



On Jan 30, 2016, at 3:32 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Rohn,


You really need to provide more information. What you provided in this message lacks any Emp#. Please give all the information required such as all the significant fields and table names. Some sample records and desired results would be helpful.


Duane Hookom, MVP

MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Fri, 29 Jan 2016 14:24:21 -0800
> Subject: [MS_AccessPros] Re: Query the most recent date!
>
>
>
> WOW, Thanks for the quick response John, that does a great job of
> getting down to just the greatest date. Guessing I need to be more
> articulate... I'm looking for the greatest date (with your
> suggestion) by employee id (License_Cert.[Emp#]) if that makes since.
>
> The data looks like:
> ID Cert_Expire_Date Cert_Title
> 0284 05/12/2011 Scissor Lift Cert
> 0284 02/11/2012 Scissor Lift Cert
> 0284 07/22/2014 Scissor Lift Cert
> 0284 01/08/2015 Scissor Lift Cert
> 0742 01/14/2011 Scissor Lift Cert
> 0742 07/28/2014 Scissor Lift Cert
> 0742 11/17/2015 Scissor Lift Cert
>
> I have been playing with your suggestion but can't figure out how to
> get the latest date for each employee ID.
>
> Sorry for not being more precise in my first explanation!
> Regards, Rohn
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links






------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/