Xavier:

Try this:

SELECT Model, Item,
MAX([Completion Date]) As [Latest Completion Date]
FROM Table2 AS T1
WHERE NOT EXISTS
  (SELECT *
   FROM Table2 AS T2
   WHERE T2.Model = T1.Model
        AND  T2.Item = T1.Item
         AND T2.[Completion Date] IS  NULL)
GROUP BY Model, Item
UNION ALL
SELECT Model, Item, [Completion Date]
FROM Table2
WHERE [Completion Date] IS NULL;

The first part of the UNION ALL operation returns those model/item rows
where there is no NULL completion date (identified by the subquery) grouping
by Model/Item and returning the latest date.  The second part of the
operation returns those roes where the completion date IS NULL.  There is no
need to group this part of the UNION ALL operation as I assume there will no
more than one open revision per model/item.  If there can be more than one
simply change the UNION ALL to a UNION operation as the latter suppresses
duplicates.  A UNION operation is slower than a UNION ALL, however, so should
only be used when necessary.

Ken Sheridan
Stafford, England

One way should be

SELECT Model, Item, Rev, CompletionDate
FROM Table2
WHERE Nz(CompletionDate,#1/1/9999#) =
   (SELECT Max(Nz(CompletionDate,#1/1/9999#))
    FROM Table2 as Temp
    WHERE Temp.Model = Table2.Model)

Thanks guys, got both methods to work!

Xavier