MS Access: How do I find an employee with the latest date with “Working” or “Reinstated” status only?

tbl_emp_mast

emp_no emp_last_name emp_first_name
1      smith          john
2      doe            jane
3      case           justin

tbl_emp_st

emp_no  st_date      emp_st
1       10/01/2020    WORKING
1       10/05/2020    QUIT
1       10/10/2020    REINSTATED
2       10/07/2020    QUIT
3       10/02/2020    WORKING

For the purpose of the combo box in the form, I wanted only those employees who are currently working. So, there should be employees # 1 and 3 from the query result. I tried this:

SELECT tbl_emp_mast.emp_no AS tbl_emp_mast_emp_no, tbl_emp_mast.emp_last_name & ", " & tbl_emp_mast.emp_first_name AS Employee, tbl_emp_st.st_date, tbl_emp_st.emp_st
FROM tbl_emp_mast INNER JOIN tbl_emp_st ON tbl_emp_mast.[emp_no] = tbl_emp_st.[emp_no];

Thank you.

Answer

Hmmm . . . I think you want the most recent emp_st. If so, then I think:

select es.*
from tbl_emp_st as es
where es.st_date = (select max(es2.st_date)
                    from tbl_emp_st as es2
                    where es2.emp_no = es.emp_no
                   ) and
      es.emp_st in ('WORKING', 'REINSTATED');