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

 

[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);