I have been trying to figure a way of solving the following problem all day yesterday. I know I can do it with vb script but there must be a way to do it in the query.
The following query returns a list of demands with attached remarks. I would like to return each dmdID only once with the most recent remark using the rmkDate column.
Here is my query which returns all remarks for all demands.
SELECT TOP 100 PERCENT tblDemand.DmdNum,
FROM tblDemand INNER JOIN
tblDemandRemarks ON tblDemand.DmdID = tblDemandRemarks.DmdID INNER JOIN
tblRemarks ON tblDemandRemarks.RmkID = tblRemarks.RmkID INNER JOIN
tblRoles ON tblRemarks.RoleID = tblRoles.roleID
ORDER BY tblDemandRemarks.DmdID, tblRemarks.RmkDate DESC