Solved

Limit query to most recent updates (round two)

Posted on 2014-04-08
8
298 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39987454
upload a copy of db with tables  tblStatus and tblProject
0
 
LVL 48

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 48

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
 
LVL 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 48

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 119

Accepted Solution

by:
Rey Obrero 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access VBA Form - Filter Multiple and/or 7 36
Combobox issue 4 27
Dlookup issue 4 16
Query 14 22
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

12 Experts available now in Live!

Get 1:1 Help Now