Get only latest date (1 Viewer)

terrytek · Jan 14, 2019

I have the following query, which I want to return ONLY the most recent test date:

Code:

SELECT qryTestingMostRecentTest.StudentFileName, Last(qryTestingMostRecentTest.TestDate) AS LastOfTestDate, qryTestingMostRecentTest.NextForm, qryTestingMostRecentTest.Class
FROM qryTestingMostRecentTest
GROUP BY qryTestingMostRecentTest.StudentFileName, qryTestingMostRecentTest.NextForm, qryTestingMostRecentTest.Class;

But it is giving me all the test dates for each student when I only want the latest.

Minty · Jan 14, 2019

Try using Max() instead of Last.

Last is a horrible function and doesn't do what you might have thought.

theDBguy · Jan 14, 2019

Hi Terry,

I agree with Minty, but you might also need to use a subquery. For example, what do you get with the following modified query?

Code:

SELECT qryTestingMostRecentTest.StudentFileName, 
    qryTestingMostRecentTest.TestDate, 
    qryTestingMostRecentTest.NextForm, 
    qryTestingMostRecentTest.Class 
FROM qryTestingMostRecentTest 
INNER JOIN SELECT qryTestingMostRecentTest.StudentFileName,
    Max(qryTestingMostRecentTest.TestDate) AS LastTestDate
FROM qryTestingMostRecentTest
GROUP BY qryTestingMostRecentTest.StudentFileName) AS SQ
ON qryTestingMostRecentTest.StudentFileName=SQ.StudentFileName
    AND qryTestingMostRecentTest.TestDate=SQ.LastTestDate;

terrytek · Jan 16, 2019

theDBguy said:

Hi Terry,

I agree with Minty, but you might also need to use a subquery. For example, what do you get with the following modified query?

Code:

SELECT qryTestingMostRecentTest.StudentFileName, 
    qryTestingMostRecentTest.TestDate, 
    qryTestingMostRecentTest.NextForm, 
    qryTestingMostRecentTest.Class 
FROM qryTestingMostRecentTest 
INNER JOIN SELECT qryTestingMostRecentTest.StudentFileName,
    Max(qryTestingMostRecentTest.TestDate) AS LastTestDate
FROM qryTestingMostRecentTest
GROUP BY qryTestingMostRecentTest.StudentFileName) AS SQ
ON qryTestingMostRecentTest.StudentFileName=SQ.StudentFileName
    AND qryTestingMostRecentTest.TestDate=SQ.LastTestDate;

I get an error "syntax error in FROM clause".

theDBguy · Jan 16, 2019

Sorry, looks like I forgot to add an opening bracket here...

Code:

SELECT...
FROM...
INNER JOIN (SELECT...
...

terrytek · Jan 16, 2019

That worked a charm. Thanks so much!

theDBguy · Jan 16, 2019

terrytek said:
That worked a charm. Thanks so much!

Hi. You're welcome. Glad to hear you got it to work. Minty and I were happy to assist. Good luck with your project.

Get only latest date (1 Viewer)

terrytek

Registered User.

Minty

AWF VIP

theDBguy

I’m here to help

terrytek

Registered User.

theDBguy

I’m here to help

terrytek

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread