Top in subgroups of a table, Any way to do so

[SkipCaryl]

I need to get out the 3 oldest dates for each Client. Generically, I'd like to enter the following:

Select User in X, Top 3 Date in X, order Date descending
From X.

Forgiveness requested for poor SQK. I would like to do this in Access as I don't know that much SQL or VisualBasic but will learn them as needed as this is a my main client's real need.

[GolferGuy]

This is what the SQL would look like:

SELECT TOP 3 tblData.UserName, tblData.DateEntered
FROM tblData
ORDER BY tblData.DateEntered DESC;

Good luck,
Vic

[SkipCaryl]

I think what you have is the TOP in a table. I need multiple tops, i.e. one set for each user. E.g. what are the last three non-green visits by each patient. From this I plan to remove those with only one or two visits and use the third visit as a "breakpoint" for eamining the data of each person with 3 or more "non-green" entries.

If this is what your query does, I am surely missing something.

[nosepicker]

From what it seems like you want, GolferGuy's query won't do. I think this is what you are looking for (using T-SQL):

SELECT A.[user], A.[date]
FROM TableX AS A JOIN TableX AS B
ON A.[user] = B.[user] AND A.[date] >= B.[date]
GROUP BY A.[user], A.[date]
HAVING COUNT(*) <= 3

This assumes that the combination of [user] and [date] creates unique records. If not, you need to add more columns to the JOIN to create uniqueness. I don't know how this would translate to Access, however.

[SkipCaryl]

Thanks for a clever approach. I don't need to use Access since Access supports partial and full SQL queries. I particularly like have a single query select out the the 3s or less rather than a subquery that basically scans the data twice.