CODEWITHSUNDEEP
datems-access
Sapvinder
Sapvinder

Reputation: 93

MS Access query - select most recent date

I have a query I'm designing which grabs data from multiple different tables. In MS Access 2010, how would I create one of the query columns so that it returns the most recent date out of a series of dates, for each user in the table:

Sample data from table:

userid: | appointment:
000001  | 05/10/2009
000001  | 05/10/2010
000001  | 05/11/2010
000002  | 05/12/2009
000002  | 30/12/2010

expected output for field query:

userid:  | appointment:
000001   | 05/11/2010
000002   | 30/12/2010

Upvotes: 4

Views: 16800

Answers (2)

user3656653
user3656653

Reputation: 1

SELECT B.Job_Emp_ID, B.JobTitle, B.Salary,B.AssignmentDate FROM tbl_Emp_Job_Assignment as B INNER JOIN tbl_Emp_Job_Assignment as A ON (B.Job_Emp_ID=A.Job_Emp_ID and B.AssignmentDate>A.AssignmentDate) GROUP BY B.Job_Emp_ID, B.JobTitle,B.Salary, B.AssignmentDate;

This works Great!It gives the Latest job assignment and Assignment Date.

Upvotes: 0

HansUp
HansUp

Reputation: 97101

SELECT userid, Max(appointment) AS most_recent
FROM YourTable
GROUP BY userid;

Upvotes: 5

Related Questions