Return only latest status update in query
Hi. I am using Access 2010 and trying to create a query that will return only the most recent status update for a project.
Here's the sql for the query.
SELECT DISTINCT qryProjectID.ProjID, tblStatus.StatusDate, Max(tblStatus.StatusDate) AS LatestDate
FROM qryProjectID INNER JOIN tblStatus ON qryProjectID.ProjID = tblStatus.ProjectID
GROUP BY qryProjectID.ProjID, tblStatus.StatusDate;
This is the result I get. I want Project 1 only once and showing the last status update date. Suggestions anyone?
Tables are tblProject with ProjID and tblStatus with ProjectID, StatusID, StatusDate, and other detail fields. Tables are joined one to many from tblProject to tblStatus.
Here's the sql for the query.
SELECT DISTINCT qryProjectID.ProjID, tblStatus.StatusDate, Max(tblStatus.StatusDate) AS LatestDate
FROM qryProjectID INNER JOIN tblStatus ON qryProjectID.ProjID = tblStatus.ProjectID
GROUP BY qryProjectID.ProjID, tblStatus.StatusDate;
This is the result I get. I want Project 1 only once and showing the last status update date. Suggestions anyone?Tables are tblProject with ProjID and tblStatus with ProjectID, StatusID, StatusDate, and other detail fields. Tables are joined one to many from tblProject to tblStatus.
PatHartman
Remove the Distinct predicate. It is interfering with the grouping.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked like a dream! Thank you!