Most recent date query

[Bellyjeans]

Hi there,

I have a problem that I'm sure there's a very simple solution to, but
I can't figure it out. I'm creating a query based on two tables:
tblStaffMain and tblRank. tblStaffMain houses employee information.
These two tables have a one-to-many relationship: one employee in
tblStaffMain can have many ranks. The two fields captured in tblRank
are rank and date of rank. When I built a query to list the employees
along with their perspective ranks, it brings up all of the ranks and
rank dates associated with that employee. Is there a way to limit the
query so that only the most recent date of ranks show instead of all
of them?

Here's my query in SQL:

SELECT tblStaffMain.LastName, tblStaffMain.FirstName,
tblStaffMain.PrimaryDivision, tblStaffMain.Status, tblRank.Rank,
tblRank.DateOfRank
FROM tblStaffMain LEFT JOIN tblRank ON tblStaffMain.StaffID =
tblRank.StaffID
WHERE (((tblStaffMain.Status)="CHAIR" Or (tblStaffMain.Status)="DEAN"
Or (tblStaffMain.Status)="FT I" Or (tblStaffMain.Status)="FT II" Or
(tblStaffMain.Status)="FT III") AND ((tblStaffMain.Active)="YES"))
ORDER BY tblStaffMain.LastName, tblRank.DateOfRank DESC;

Any help would be much appreciated!

 

[Jeff Boyce]

One approach might be to first join the two tables together, then use the
<Totals> button to select the "Max" [DateOfRank].

Regards

Jeff Boyce
Microsoft Office/Access MVP