MS Access query to return the most recent record from a table?

Hello Experts.

Well, apparently I lack the ability to learn from the hundreds of posts that cover this same question so I am reaching out for some guidance.

I have two tables:  tbl_emp and tbl_emp_assnmt.  tbl_emp contains all of our employees and tbl_emp_assnmt holds all their assignments.  Each employee can have multiple assignments.  I would like to pull just the most recent tbl_emp_assnmt for each employee.  Here's the query that pulls everything:

FROM tbl_emp

LEFT JOIN tbl_emp_assnmt

ON  tbl_emp.emp_id_pk = tbl_emp_assnmt.emp_id_fk

Open in new window

I would like the most recent assignment based on the Assignment Entry Date (tbl_emp_assnmt.entry_dt) field.  Once catch though, not everybody has assignments yet, and I need them to be in the results of this query also.  So this query should return the most recent assignment for each employee, and just the employee (tbl_emp.emp_id_pk) if there aren't any assignments yet.

I hope this makes sense.  Any help would be greatly appreciated.  

I'm using MS Access 2010.

Who is Participating?
This works, provided that you don't have multiple assignments for the same employee on the most recent date, as you'd then get multiple result lines for that employee.
SELECT E.emp_id_pk, sq.maxDate AS entry_dt, A.unit_ddl, A.dept_ddl
 (select emp_id_fk, max(entry_dt) as MaxDate 
  from tbl_emp_assnmt
  group by emp_id_fk)  AS sq 
ON E.emp_id_pk = sq.emp_id_fk) 
LEFT JOIN tbl_emp_assnmt AS A 
ON (sq.MaxDate = A.entry_dt) AND (sq.emp_id_fk = A.emp_id_fk);

Open in new window

The above tested in Access 2010, producing sample results as shown below. Note the  duplicate row for employeeID=2, who has two assignments both starting on 1st March.
ferguson_jeraldAuthor Commented:
Thanks Simon!  Everybody gets one assignment per day, never anymore so this works great for what I need.  I somebody were to get more than one I would need to see that as well.
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.