Query: Getting Max Date for each person

[psycoperl]

I am trying to obtain the most recent record for each StudentID. tnDateEntered is a date/time that contains a timestamp. I can not determine how to modify the query below.

Guidance is appreciated.

SELECT 
  tblStudentTestNeeds.tnRecordID, 
  tblStudentTestNeeds.tnStudentID, 
  tblTestInfo.tiOfficeDescription, 
  tblStudentTestNeeds.tnTestCode, 
  tblStudentTestNeeds.tnDateEntered
FROM 
  tblTestInfo INNER JOIN tblStudentTestNeeds 
  ON tblTestInfo.tiTestID = tblStudentTestNeeds.tnTestCode
ORDER BY tblStudentTestNeeds.tnDateEntered DESC;

 

[Joe4]

Take a look at creating Aggregate (Totals) Queries in Access's built-in help files. You can get things like Counts, Sums, Maximums, Minimums, etc of a "grouped" bunch of records.

 

[psycoperl]

Take a look at creating Aggregate (Totals) Queries in Access's built-in help files. You can get things like Counts, Sums, Maximums, Minimums, etc of a "grouped" bunch of records.

I had tried but it was not producing the value I was looking for.

Instead of producing just one record per person, it gives me alot more.

I should receive for each tnStudent the most recent tnDateEntered and the TnTestCode and tiOfficeDescription for it.

SELECT tblStudentTestNeeds.tnStudentID, Max(tblStudentTestNeeds.tnDateEntered) AS MaxOftnDateEntered, tblStudentTestNeeds.tnTestCode, tblTestInfo.tiOfficeDescription
FROM tblStudentTestNeeds INNER JOIN tblTestInfo ON tblStudentTestNeeds.tnTestCode = tblTestInfo.tiTestID
GROUP BY tblStudentTestNeeds.tnStudentID, tblStudentTestNeeds.tnTestCode, tblTestInfo.tiOfficeDescription;

 

[Joe4]

You'll probably need to do this in two queries (or one query with a subquery).

The first query (or subquery) will be the Aggregate Query where you just have the Person ID field (Group on that) and the Date field (take Max of that). Then link that subquery back to your original table on these two fields, returning all the fields you want.

 

[alansidman]

This video works on a similar concept to what you are looking for (I think).

http://www.datapigtechnologies.com/flashfiles/topvaluespergroup.html