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:

SELECT 
tbl_emp.emp_id_pk, 
tbl_emp_assnmt.entry_dt, 
tbl_emp_assnmt.unit_ddl, 
tbl_emp_assnmt.dept_ddl
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.

Thanks,
J
ferguson_jeraldAsked:
Who is Participating?
 
SimonCommented:
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
FROM (tbl_emp AS E LEFT JOIN 
 (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.
LatestAssignment.png
0
 
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.
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.