Get latest date for each task record

[Dorian]

I have a table that stores assignments of tasks to teams. Tasks may be
reassigned so there may be many teams assigned to a task - though only one
at any point in time.
I need to find the last assigned team for each task.
Table layout
TaskID
TeamID
AssignDate
I'm sure there must be some simple way to do it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

Assumptions:
-- Table is named Assignments
-- Table and field names consist of only letters, numbers, and the
underscore character.
-- If AssignDate does not contain a time, then at most a task can be
assigned once a day.

SELECT TaskID, TeamID, AssignDate
FROM Assignments INNER JOIN
(SELECT TaskID, Max(AssignDate) as LastAssigned
FROM Assignments
GROUP BY TaskID) as T
ON Assignments.TaskID = T.TaskID
AND Assignments.AssignDate = T.LastAssigned

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

First query --
SELECT TaskID, Max([AssignDate] AS MaxOfAssignedDate
FROM YourTable
GROUP BY TaskID;

Second query --
SELECT TaskID, TeamID, AssignDate
FROM YourTable INNER JOIN FirstQuery ON (YourTable.TaskID =
FirstQuery.TaskID) AND (YourTable.AssignDate = FirstQuery.MaxOfAssignedDate);