Solved

Limit query to most recent updates (round two)

Posted on 2014-04-08
8
308 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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