SQL how to join a MAX on a SELECT sub-query ?

Hi.  Forgive the title I gave this question ... I'm not sure how to ask it except through this example ---
I have a table of employees EMP:
EmpID   LastName   Status
------|----------|-------
39    | Carter   | A
40    | Reagan   | I
41    | Bush     | A
42    | Clinton  | A
44    | Obama    | A

Open in new window

and a table of their dates of attendance ATTEND:
id  EmpID   Attended
---|------|-----------
1  | 44   | 08/01/2016
2  | 42   | 06/01/2016
3  | 41   | 06/01/2016
4  | 40   | 04/01/2016
5  | 39   | 04/01/2016
6  | 39   | 03/01/2016

Open in new window

I need to create a SELECT that shows which active status "A" employees have not attended since 05/01/2016  (mm/dd/yyyy).  The result, I imagine, should look like this:
EmpID   LastName   lastdate
------|----------|-----------
39    | Carter   | 04/01/2016

Open in new window

I've tried several ways of doing this with joined "select" statements, but none seem to work right.
Any suggestions?
Thanks!
Rob RudloffIT Development SpecialistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
YZlatConnect With a Mentor Commented:
try

SELECT e.EmpID, e.LastName, a.Lastdate FROM EMP as e
INNER JOIN (SELECT EmpID, MAX(Attended) As Lastdate FROM ATTEND GROUP BY EmpID) as a ON e.EmpID=a.EmpID
WHERE e.Status='A' AND a.Lastdate < '05/01/2016'

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can also use criteria under the date
     dMax("Attended", "Attend", "EmpID=" & [EmpID])

Open in new window

0
 
Rob RudloffIT Development SpecialistAuthor Commented:
Thanks YZlat!   Seems simple now that you have shown me.  (I was making it way too complicated, and I had the syntax of my JOIN wrong.)
0
 
YZlatCommented:
Glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.