Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Limit query to most recent updates (round two)

Posted on 2014-04-08
8
Medium Priority
?
315 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 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 800 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1200 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

972 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