Most recent date query   

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!

date: Thu, 25 Sep 2008 09:31:12 -0700 (PDT)   author:   Bellyjeans

Re: Most recent date query   

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

"Bellyjeans"  wrote in message 
news:4d6a2589-a863-42b7-85a8-0b85f99ab0a0@26g2000hsk.googlegroups.com...
> 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!

date: Thu, 25 Sep 2008 09:39:50 -0700   author:   Jeff Boyce