Solved

Limit query to most recent updates (round two)

Posted on 2014-04-08
8
311 Views
Last Modified: 2014-04-13
Hi.  I am using Access 2010 and trying to create a query that will return only the most recent status update for a project.  Sorry I don't understand sql very well.
Here's the sql for the query.

SELECT tblStatus.ProjectID, Max(tblStatus.StatusDate) AS LatestDate
FROM tblStatus
GROUP BY tblStatus.ProjectID;

This works wonderfully (thanks PortletPaul) but when I try to add columns to the query to show the Project Name or description of the last status update ( tblProject.name and  tblStatus.statusdetail) I get multiple results for each project and also projects that haven't had any status updates yet.  See below.  How can I exclude projects that have no status updates and show only the most recent status update for those that do?

Here's the current sql
SELECT tblStatus.ProjectID, tblProject.ProjName, tblStatus.StatusCategory, tblStatus.StatusDetail, Max(tblStatus.StatusDate) AS LatestDate
FROM tblProject LEFT JOIN tblStatus ON tblProject.ProjID = tblStatus.ProjectID
GROUP BY tblStatus.ProjectID, tblProject.ProjName, tblStatus.StatusCategory, tblStatus.StatusDetail;

screen shot of results
0
Comment
Question by:esbyrt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39987454
upload a copy of db with tables  tblStatus and tblProject
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 39987746
To exclude some records from consideration, use a "where clause"
for example:

WHERE  tblStatus.StatusCategory IS NOT NULL

this will remove the first 8 rows of your image. If it does not then try this

WHERE  tblStatus.StatusCategory <> ''
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39987750
For the other needs you need to do something quite different - but before launching into that you need to understand what GROUP BY does:

This clause:

GROUP BY  tblStatus.ProjectID, tblProject.ProjName, tblStatus.StatusCategory, tblStatus.StatusDetail

evaluates all the permutations of data for those 4 fields and eradicates any rows that are EXACT REPEATS (i.e ALL 4 of those fields are considered)

so these 2 example rows ARE DIFFERENT to all other rows and it is correct that they are both listed:

1 First Project Approved
1 First Project Approved  Approved

Once those rows are include THEN it will calculate the MAX(tblStatus.StatusDate) for all data that matches to  tblStatus.ProjectID, tblProject.ProjName, tblStatus.StatusCategory, tblStatus.StatusDetail of rows

In short, the more fields you include in a group by, the higher the likelihood you will get more rows.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39987770
I think this should work, my access skills aren't great:
SELECT
      S.ProjectID
    , P.ProjName
    , S.StatusCategory
    , S.StatusDetail
    , L.LatestDate
FROM ((tblStatus AS S
      INNER JOIN (
            SELECT
                  tblStatus.ProjectID
                , MAX(tblStatus.StatusDate) AS LatestDate
            FROM tblStatus
            GROUP BY
                  tblStatus.ProjectID
      ) AS L
            ON S.ProjectID = L.ProjectID
                  AND S.StatusDate = L.LatestDate)
      INNER JOIN tblProject AS P
            ON S.ProjectID = P.ProjectID)
WHERE  S.StatusCategory IS NOT NULL
;

Open in new window

What is done here is to move that earlier max(date) query into a subquery (or really a "derived table")

By linking this back to the status rows it should* restrict you to one row per project but now you have all columns of the status table for that max(date)

* I am not 100% sure it will as I'm not familiar with the data but if tblStatus.StatusDate is date with time (as I hope) then it should be ok.
0
 

Author Comment

by:esbyrt
ID: 39992606
Hi folks.  Okay, the WHERE IS NOT NULL worked fine for eliminating the blank status rows.  Thanks! I am uploading a copy of the database.  This is a work in progress so it isn't pretty yet.  Ignore the login screen that opens, it isn't locked into anything yet.  It is the Copy of qryStatusDates that I have been working on.  PortletPaul, when I tried the code you offered and ran the query I was prompted for P.ProjectID.  I put in project number 2 and got 15 records for project 2, which isn't correct.  There are only a couple status updates for 2 and I got status updates from other projects with it.
Thanks for having a look!
ARv2.accdb
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39992898
sorry but I don't run/use Access

I'm not sure how you could get more rows than exist, this is how that query is designed:

the innermost subquery (L) simply lists the projectid plus latest status date (it's the query in your question).

that list is then joined back to the status table, using both projectid and the latest date, so at this point only the records of the latest status updates is available.

This is then joined to the project table via projectid so all project table and all status table fields are available (but still only for the the lateststatus updates).

Projects that have had no status update won't be listed by the way.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 39993008
try this query


SELECT P.ProjID, P.ProjName, Q.StatusCategory, Q.StatusDetail, Q.LatestDate
FROM tblProject AS P INNER JOIN
(
SELECT S.ProjectID, S.StatusCategory, S.StatusDetail, L.LatestDate
FROM tblStatus AS S INNER JOIN
           (
           SELECT tblStatus.ProjectID, MAX(tblStatus.StatusDate) AS LatestDate
           FROM tblStatus GROUP BY tblStatus.ProjectID
            )  AS L        
           ON S.StatusDate = L.LatestDate AND S.ProjectID = L.ProjectID
           WHERE S.StatusCategory Is Not Null
)  AS Q ON P.ProjID = Q.ProjectID
ORDER BY P.ProjID;
ARv2.accdb
0
 

Author Closing Comment

by:esbyrt
ID: 39997742
That works perfectly.  Thanks so much!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

632 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