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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.