-
Top in subgroups of a table, Any way to do so
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.
-
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
-
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.
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|