Query to find latest entry in table based upon multiple criteria

[Bigfella928]

Hi, All.
Could use some help here please. Complete novice at SQL or Access for that matter
I have a table of records that stores training card information including date date added, expiry date, employee name, card type, Card Serial No.
Each employee could have multiple cards of the same type over time and the serial No. tends to be the same.
I want to create a report of expired cards which is easy.
However, I want to ignore an expired card if a new card has been obtained.
I have tried a max query which, on the data I have, reports back all of the cards which match the card type criteria but do not get the last card for that employee.
In one case this means three entries on one employee.
The question therefore is how do I now select the latest of these records please, such that I end up with the latest card of a given type.

Below is the SQL I have at the moment
SELECT Tbl_EmpCards.Employee_Name, Max(Tbl_EmpCards.Date_Added) AS [Max], Tbl_EmpCards.CSCS_YN, Tbl_EmpCards.Card_Name, Tbl_EmpCards.Card_GenName, Tbl_EmpCards.Card_No, Tbl_EmpCards.Card_Duration, Tbl_EmpCards.Card_Expiry
FROM Tbl_EmpCards
GROUP BY Tbl_EmpCards.Employee_Name, Tbl_EmpCards.CSCS_YN, Tbl_EmpCards.Card_Name, Tbl_EmpCards.Card_GenName, Tbl_EmpCards.Card_No, Tbl_EmpCards.Card_Duration, Tbl_EmpCards.Card_Expiry
HAVING (((Tbl_EmpCards.Card_Name) Not Like "*HS&E*"));

Thanks in advance for any assistance please.

 

[theDBguy]

Hi. Welcome to AWF!

You might have to use a subquery.

 

[Bigfella928]

hi the DBguy. Thanks for the welcome.
Pretty sure I will need to but have tried numerous ways without success so far.

 

[plog]

How do you know if a card has expired?

 

[Bigfella928]

Hi plog.
I have an expiry date. I am thinking I have to find the last entry for each person by card type which will get rid of previous similar cards. Then do a <date() on the remaining. Just not sure how to do the first bit.
Cheers

 

[plog]

What is the serial number fieldname, what is the expirationfield name?

 

[Pat Hartman]

If the expired cards have an expiration date and the unexpired cards have null expiration dates, then that is your criteria.

SELECT Tbl_EmpCards.Employee_Name, Tbl_EmpCards.CSCS_YN, Tbl_EmpCards.Card_Name, Tbl_EmpCards.Card_GenName, Tbl_EmpCards.Card_No, Tbl_EmpCards.Card_Duration, Tbl_EmpCards.Card_Expiry
FROM Tbl_EmpCards

WHERE Tbl_EmpCards.Card_Expiry Is Null;

 

[arnelgp]

show some data in excel.

 

[Bigfella928]

Hi All.
Pat Hartman's solution worked great thanks.
Thanks so much for the help all.

 

[theDBguy]

Hi All.
Pat Hartman's solution worked great thanks.
Thanks so much for the help all.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

You're welcome