Solved

Limit query to most recent updates (round two)

Posted on 2014-04-08
8
303 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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