• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Access - Get latest DateAuthorized for each Emp_ID

Hello all

I am attempting to define a Many to Many table link and get a query of the latest DateAuthorized for each employee.

I've tried Last, Max etc.  but I really can't grasp it.

SELECT Link_EmpToActAuthLevel.Emp_ID, Link_EmpToActAuthLevel.ActAuthLevelID, Link_EmpToActAuthLevel.DateAuthorized, Link_EmpToActAuthLevel.LinkEmpToActAuthLevelID, qry_Employees.EmpFullName, qry_Employees.EmpFullNickname, ActivityAuthorizationLevel.ActAuthLevel
FROM ActivityAuthorizationLevel INNER JOIN (Link_EmpToActAuthLevel INNER JOIN qry_Employees ON Link_EmpToActAuthLevel.Emp_ID = qry_Employees.EmpID) ON ActivityAuthorizationLevel.ActAuthLevelID = Link_EmpToActAuthLevel.ActAuthLevelID
GROUP BY Link_EmpToActAuthLevel.Emp_ID, Link_EmpToActAuthLevel.ActAuthLevelID, Link_EmpToActAuthLevel.DateAuthorized, Link_EmpToActAuthLevel.LinkEmpToActAuthLevelID, qry_Employees.EmpFullName, qry_Employees.EmpFullNickname, ActivityAuthorizationLevel.ActAuthLevel;

Open in new window

0
wlwebb
Asked:
wlwebb
  • 2
2 Solutions
 
mbizupCommented:
Save this as a query named qryInitial (or whatever name you want to give it):
SELECT Link_EmpToActAuthLevel.Emp_ID AS EmployeeID, Link_EmpToActAuthLevel.ActAuthLevelID, Link_EmpToActAuthLevel.DateAuthorized AS AuthorizationDate, Link_EmpToActAuthLevel.LinkEmpToActAuthLevelID, qry_Employees.EmpFullName, qry_Employees.EmpFullNickname, ActivityAuthorizationLevel.ActAuthLevel
FROM ActivityAuthorizationLevel INNER JOIN (Link_EmpToActAuthLevel INNER JOIN qry_Employees ON Link_EmpToActAuthLevel.Emp_ID = qry_Employees.EmpID) ON ActivityAuthorizationLevel.ActAuthLevelID = Link_EmpToActAuthLevel.ActAuthLevelID
GROUP BY Link_EmpToActAuthLevel.Emp_ID, Link_EmpToActAuthLevel.ActAuthLevelID, Link_EmpToActAuthLevel.DateAuthorized, Link_EmpToActAuthLevel.LinkEmpToActAuthLevelID, qry_Employees.EmpFullName, qry_Employees.EmpFullNickname, ActivityAuthorizationLevel.ActAuthLevel;

Open in new window



Then run this query to get your employeeID and latest date:

SELECT MAX(AuthorizationDate) AS MaxAuthDate, EmployeeID
FROM qryInitial
GROUP BY EmployeeID

Open in new window



Note:  I'm going on the assumption that the query you posted is a working query, and that you just want the EmployeeID and Max Authorization Date from it.
0
 
mbizupCommented:
If you need additional fields, you will have to use a subquery like this:

SELECT q1.*
FROM qryInitial q1
INNER JOIN 
(SELECT MAX(AuthorizationDate) AS MaxAuthDate, EmployeeID
FROM qryInitial
GROUP BY EmployeeID) q2
ON q1.EmployeeID = q2.EmployeeID AND q1.AuthorizationDate= q2.MaxAuthDate

Open in new window

0
 
wlwebbAuthor Commented:
Thank you mbiz.... I really struggle with those subqueries.
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now