Solved

Return only latest status update in query

Posted on 2014-04-07
3
257 Views
Last Modified: 2014-04-08
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;
picture of resultsThis 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.
0
Comment
Question by:esbyrt
3 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39984595
Remove the Distinct predicate.  It is interfering with the grouping.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39984680
if you want the project just once then remove  tblStatus.StatusDate as a column and in the group by

SELECT qryProjectID.ProjID,  Max(tblStatus.StatusDate) AS LatestDate
FROM qryProjectID INNER JOIN tblStatus ON qryProjectID.ProjID = tblStatus.ProjectID
GROUP BY qryProjectID.ProjID;

ps, never use select distinct when also doing a group by - it's a complete waste of time
0
 

Author Closing Comment

by:esbyrt
ID: 39987241
Worked like a dream!  Thank you!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now