Is there a way to find the records with the 4 most recent dates?

[terrytek]

I need to find instances of students that have three or four tests by their three or four most recent test dates. Is there a way to query with that criterion?

Thanks.

 

[theDBguy]

Hi,

Sounds like you could use a "Top N Per Group" query.

 

[Galaxiom]

Sounds like you could use a "Top N Per Group" query.

Googling turns up a lot of unsatisfactory attempts to do this in Access.

I think it would have to be done as an aggregate query then a TOP query on each of the groups. I don't think it can written just as a query in Access. The TOP queries would have to be dynamically built in code.

It can be done in SQL Server with a PARTITION. (Partition function in Access is a different animal.)

 

[isladogs]

I've successfully used the Top N per group subquery approach explained by Allen Browne here http://allenbrowne.com/subquery-01.html#TopN

 

[sxschech]

Here is one I did based on Allen Browne's link example to get the 10 most recent documents by date within a group based on first 11 chars of the document number:

SELECT DocumentNo, DateModified
FROM (SELECT left(qryLetters.DocumentNo,11) AS DOC11, qryLetters.DateModified, qryLetters.DocumentNo
FROM qryLetters
WHERE qryLetters.DocumentNo IN
   (SELECT TOP 10 DocumentNo                            
   FROM qryLetters AS Dupe                              
   WHERE left(Dupe.DocumentNo,11) = left(qryLetters.DocumentNo,11)        
   ORDER BY Dupe.DateModified DESC, Dupe.DocumentNo DESC) 
ORDER BY left(qryLetters.DocumentNo,11), qryLetters.DateModified, qryLetters.DocumentNo)  AS TOP10
ORDER BY DOC11, getnumber([DocumentNo]);