Rohn-
Like this:
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);
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
(Paris, France)
On Jan 29, 2016, at 5:30 PM, reverson@maintainer.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have a Query that I only want to show the most recent date for each employee record. I have tried in the GroupBy field.... Max(License_Cert.Cert_Expire_Date) but that does not limit the records. Is there some other way to limit the query records to the most recent date?
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, Max(License_Cert.Cert_Expire_Date) AS MaxOfCert_Expire_Date
FROM License_Cert INNER JOIN dbo_EMPLOYEE ON License_Cert.[EMP#] = dbo_EMPLOYEE.ID
GROUP BY License_Cert.[Emp#], License_Cert.Cert_Date, License_Cert.Cert_Title, License_Cert.Cert_NO, License_Cert.Issuing_Authority, Year([Cert_Date]), [First_Name] & ' ' & [Last_Name]
HAVING (((License_Cert.[Emp#]) Like "*" & [Enter Employee Number:] & "*") AND ((License_Cert.Cert_Title) Like "*" & [Enter Cert Title:] & "*"));
Thanks for your help and insights on this!
Rohn
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar