Getting max Commitment_date in a query

I should have used a better example.  The Task_Commit_Date are not always the same as it appears above.  They could be any date as follows:

Commitment_ID     Task_Commit_Date     Commitment_Date (all dates being the latest)
875                           11/1/2004                11/19/2004
875                           11/12/2004                11/19/2004
875                           11/7/2004                11/19/2004
881                           11/10/2004                   1/1/2005
881                           10/1/2004                      1/1/2005
881                           11/2/2004                      1/1/2005
881                           11/4/2004                      1/1/2005

Something like this, I imagine:

SELECT Commitment.Commitment_ID, Commitment_Tasks.Task_Commit_Date, Max(Commitment_Date.Commitment_Date)
FROM (Commitment INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID) INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE (((Commitment.Commitment_Completed)=False) AND ((Commitment_Tasks.Task_Completed)=False))
GROUP BY Commitment.Commitment_ID
ORDER BY Commitment_Tasks.Task_Commit_Date DESC;

Is that what you were looking for?

Sorry, the GROUP BY part should read:
GROUP BY Commitment.Commitment_ID,  Commitment_Tasks.Task_Commit_Date

Your query only returns the following:

Commitment_ID      Task_Commit_Date      Expr1002
875      11/15/2004      11/19/2004
881      11/1/2004        1/1/2005

That's the same problem that I had?  It only wants to return one record (even when there are more)  for each max Commitment_Date.

My first posting was misleading.  My second posting shows all the task dates are different.  In the real world,  each task can have any date that is put in so it can be the same or different then another task.  Your query works GREAT!!!  Thanks

I added all the fields that need visibility and DISTINCT.  Here is the final query.
--------------------------------------------------------------------------------------------
SELECT DISTINCT Commitment.Commitment_ID, Commitment_Tasks.Commitment_Tasks_ID, Commitment.Project_Name, Commitment.DTL_Owner, Commitment_Tasks.Task, Commitment_Tasks.DTL_Assignee, (SELECT Max(Commitment_Date) FROM Commitment_Date As C WHERE C.Commitment_ID=Commitment.Commitment_ID) AS LastCommitDate, Commitment_Tasks.Task_Commit_Date, Commitment_Tasks.Task_Commit_Date_Accept AS Accepted
FROM (Commitment INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID) INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE (((Commitment.Commitment_Completed)=False) AND ((Commitment_Tasks.Task_Completed)=False))
ORDER BY Commitment_Tasks.Task_Commit_Date;

Here is the Query Output
----------------------------
Commitment_ID      Commitment_Tasks_ID      Project_Name      DTL_Owner      Task      DTL_Assignee      LastCommitDate      Task_Commit_Date      Accepted
912      1434      FactoryWorks Eval      Bob Schmitt      FactoryW      Bob Schmitt      1/2/2005      1/2/2004      No
881      1227      Gen 2 MiBs      Bob Schmitt      Gen 2 task 1      Bob Schmitt      1/1/2005      11/1/2004      Yes
881      1228      Gen 2 MiBs      Bob Schmitt      Gen 2 task 2      Bob Schmitt      1/1/2005      11/1/2004      Yes
1004      1435      no date      Bob Schmitt      a      Bob Schmitt      1/1/2005      11/1/2004      No
1004      1436      no date      Bob Schmitt      a      Bob Schmitt      1/1/2005      11/1/2004      No
875      1216      MECC Pulse Tester      Bob Schmitt      xxx      Bob Schmitt      11/19/2004      11/15/2004      Yes
875      1433      MECC Pulse Tester      Bob Schmitt      Task2      Bob Schmitt      11/19/2004      11/19/2004      No
1004      1437      no date      Bob Schmitt      c      Bob Schmitt      1/1/2005      1/1/2005      No