Finding lastest status from a series of records

[sms28]

I have a project report by resource that shows the tasks a resource has worked and the status. The task table for a resource resembles the following.

1. Resource A -- 8/1/03 -- Status In Progress
2. Resource A -- 8/8/03 -- Status In Progress
3. Resource A -- 8/15/03 -- Status Completed.

I'm having a problem getting the latest status which would be Completed in the example above. The record source for the report is a query. When I have ascending and last on the reporting date, I get 2 records (2 and 3) and not just the one record (3) with the latest status. How should I resolve?

Thanks in advance.

 

[jdegeorge]

sms28

I have a query that links my item table with the status table and it uses the max function. Here's the code:

SELECT tblStatus.IncidentID, Max(tblStatus.StatusDate) AS [Status Date], Last(tblStatus.Status) AS Status
FROM tblGaps INNER JOIN tblStatus ON tblGaps.GapID = tblStatus.IncidentID
WHERE (((tblGaps.DateClosed) Is Null))
GROUP BY tblStatus.IncidentID
ORDER BY tblStatus.IncidentID, Max(tblStatus.StatusDate) DESC;

What you're looking to do is get the MAX of the date field and the result will be the latest status record. Hope this helps.

Jim DeGeorge