How to get Last Record from related table

[Jamaluddin Jamal]

I have two tables, tblEmp and tblEmpPositions. tblEmp has fields like EmpID, EmpName, EmpFather and EmpDOB. tblEmpPositions has fields like PositionsID, EmpID, EmpDesign, StartDate and EndDate. Scenario is 1 Employee worked on different Positions for specified period of Time. What I want to get the Most Recent (or the Last Entered Position) of an Employee.

The end result like:

tblEmp.EmpID, tblEmp.EmpName, tblEmp.EmpFather, tblEmp.EmpDOB, tblEmpPositions.EmpDesign, tblEmpPositions.StartDate, tblEmpPositions.EndDate

Thanks in Advance

 

[Ranman256]

make a query Q1, to get the Max date:
select EmpID, Max(StartDate) from tblEmpPositions

then in Q2, join Q1 to the tEmp table to get that employee:
select * from tEmp, Q1 where tEmp.EmpID = Q1.EmpID

if you want it for 1 single Emp, put in a Where clause in Q1.

 

[Jamaluddin Jamal]

Already goes through this way, but the Access responded "Your query does not include the specified expression 'EmpID' as part of an aggregate function."

 

[Jamaluddin Jamal]

make a query Q1, to get the Max date:
select EmpID, Max(StartDate) from tblEmpPositions

then in Q2, join Q1 to the tEmp table to get that employee:
select * from tEmp, Q1 where tEmp.EmpID = tblEmpPositions.EmpID

if you want it for 1 single Emp, put in a Where clause in Q1.

Already goes through this way, but the Access responded "Your query does not include the specified expression 'EmpID' as part of an aggregate function."

 

[Ranman256]

sorry, I had an error on the Q2.
I fixed it.

 

[Jamaluddin Jamal]

sorry, I had an error on the Q2.
I fixed it.

@Ranman256, situation is same :banghead:, can you please check it with the database file I attached.

 

[isladogs]

Hi

As Ranman256 is offline, I've just completed this for you using his approach.

There are now 2 queries:
1. qryEmpMaxStartDate - finds the latest position for each employee
2. qryEmpMostRecentPosition - uses the first query together with the 2 tables to give you what you wanted

Updated version attached

 

[Jamaluddin Jamal]

Hi

As Ranman256 is offline, I've just completed this for you using his approach.

There are now 2 queries:
1. qryEmpMaxStartDate - finds the latest position for each employee
2. qryEmpMostRecentPosition - uses the first query together with the 2 tables to give you what you wanted

Updated version attached

Thanks Ridders, its working...