View Most Current

[DevlinM]

I have a table Personnel and a Table EmploymentStatus.
I want to write a query that will show all Personnel with most current
Employment Status Only. Any help is always appreciated.

Tables are as follows:

Tbl_Personnel
att_PersID
att_PersNameLast

Tbl_EmpStatus
att_ESID
att_ES_FK_PersID
att_ES_FK_BusStatusID
att_ESDate

 

[PieterLinden via AccessMonster.com]

I have a table Personnel and a Table EmploymentStatus.
I want to write a query that will show all Personnel with most current
Employment Status Only. Any help is always appreciated.

Tables are as follows:

Tbl_Personnel
att_PersID
att_PersNameLast

Tbl_EmpStatus
att_ESID
att_ES_FK_PersID
att_ES_FK_BusStatusID
att_ESDate

One way... I think there's a cleaner way to do it... check out Michel Walsh's
post on access web.
www.mvps.org/access in the Queries section...

qselLatestPromotion:
SELECT EmpPosition.EmployeeID, Max(EmpPosition.PromoteDate) AS
LatestPromotionDate
FROM EmpPosition
GROUP BY EmpPosition.EmployeeID;

SELECT EmpPosition.EmployeeID, EmpPosition.Position
FROM EmpPosition INNER JOIN qselLatestPromotion ON (EmpPosition.PromoteDate =
qselLatestPromotion.LatestPromotionDate) AND (EmpPosition.EmployeeID =
qselLatestPromotion.EmployeeID);

 

[Daryl S]

DevlinM -

Try this:

SELECT att_PersID, att_PersNameLast, att_ESID, att_ESDate,
att_ES_FK_BusStatusID
From Tbl_Personnel INNER JOIN Tbl_EmpStatus ON Tbl_Personnel.att_PersID =
Tbl_EmpStatus.att_ES_FK_PersID
WHERE Tbl_EmpStatus.att_ESDate = (SELECT Max(att_ESDate) from Tbl_EmpStatus
AS ES WHERE ES.att_ES_FK_PersID = Tbl_EmpStatus.att_ES_FK_PersID);