• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Need help with an Access2007 query

Hi Experts,
I want to modify the query below, which I use in my Access2007 application.  I want the query to only return the latest test taken base on the DateTaken field.  So if a student has taken the same test three times, I only want the query to return one record (the latest record based on a Date field).  How can I do this?

SELECT * FROM TestsTaken WHERE [StudentID] = 100 AND [TestName] = 'WeeklyExam'

Thanks in advance,
1 Solution
SELECT * FROM TestsTaken WHERE [StudentID] = 100 AND [TestName] = 'WeeklyExam'

And TestDate=(Select MAX(TestDate) from TestsTaken WHERE [StudentID] = 100 AND [TestName] = 'WeeklyExam')

In above I called your Date field TestDate
Rey Obrero (Capricorn1)Commented:

FROM  TestsTaken As T
Inner Join
(Select MAX(T2.TestDate) as MaxTestDate, T2.[StudentID], T2.[TestName] from TestsTaken as T2
  Where T2.[TestName] = 'WeeklyExam'
  Group By  T2.[StudentID], T2.[TestName]) As T3
ON T.[StudentID]=T3.[StudentID] And T.TestDate=T3.MaxTestDate
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now