Query to get max date

[mhisma]

Hello everyone,

I have tables containing the data ( Personal Role) below

EmpID EmpName Date Title
000123 Mark 02-Jun-2005 Engineer
000234 Mark 03-Jul-2006 Team Manager
000456 Mark 04-Aug-2008 District Manager


I just wanna take the latest role of Mark (District Manager)

when Using SQL query, I usually write this

Select * from Personal Role A
where date = (select max(date) from Personal_Role AA
where a.empname = aa.empname
group by empname)
or using Row Number partition by date

I try to use the same query in access, but it didnt work and I cant find the same function as SQL row number in access

Kindly need your help

 

[Kempes]

A simple way would be to create 2 querys.

Query 1) in design view, from your table bring on your Empname and date. Select Totals, group by empname and choose Max (from the drop down list) for the date.

query 2) in design view. Bring on your table and query 1. Link the date from your table to maxofdate from query 1, then add all fields you wish to show.

Query 2 will be the results you want.

Hope this helps you

Kempes

 

[mhisma]

query to get max date

great !!
it works very well

many thanks for the help