Is there a way to find the records with the 4 most recent dates? (1 Viewer)
- Thread starter terrytek
- Start date
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
MVP / VIP
- Local time
- Today, 05:29
- Joined
- Jan 14, 2017
- Messages
- 18,216
I've successfully used the Top N per group subquery approach explained by Allen Browne here http://allenbrowne.com/subquery-01.html#TopN
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:
Code:
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]);Similar threads
Solved
Age Deomgraphics
