3 most recent dates

[rmcafee]

I'm trying to pull the three most recent dates for student's courses they took in a specific content area. I already have the content area covered. My data includes a date for each course in this area but each student could have taken courses on many different dates and could have taken more than 3 courses total. I just need the 3 most recent courses that each student took to be listed in the query and drop the older courses. If there is another way to do this please, bring it on. Thanks for any help.

 

[jleach]

SELECT TOP 3 * FROM MyTable ORDER BY DateField DESC;

Something like that ought to do (not sure how to do it in the query designer though, and an explicit field list rather than * is recommended...)

 

[isladogs]

If the query is for individual students, sort the dates in descending order and in the properties window set top values = 3

If you want to do that for several students at once, it's a bit more tricky!

EDIT: Jack got there faster using SQL - my version is using the query designer

 

[jdraw]

Here's a link to an example TOP N that may be helpful.

 

[jleach]

Ah, yes... top 3 per group (top 3 per student):

http://www.allenbrowne.com/subquery-01.html#TopN

(again, not sure how to do that in the query designer...)

 

[isladogs]

Using Allen's example

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
  [COLOR="Red"] (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) [/COLOR]
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

The subquery part shown in RED goes in the criteria row for the relevant field - in this case OrderID. The start/end brackets are REQUIRED

 

[rmcafee]

Thanks all. I have this very close to working for what I need. I ran into a problem because my SIS query pulls the data from our Student Information System (SIS) by "year", so since some students may have 4 courses in only two years the database pulls four returns for some students in Access.
I have a month column too, but not sure if I can get this to pick the top three since it's two columns. IF you have any ideas on this one let me know. I would think the columns would have to be combined on the front end? I'm going to continue to work on the front end to see if I can query the full dates from our (SIS) so that it will be easier to pull the top three dates. I may run into the same problem because these same course will likely have identical dates.

Here is what I came up with and works with limited success
This is only the additional part I added to the existing SQL for the query.I'm not an expert user so this may look a bit crude to experts.
thanks

FROM [Spanish Course History]
WHERE ((([Spanish Course History].[Calendar Year]) In (SELECT TOP 3 [Calendar Year]
FROM [Spanish Course History] AS [Dupe]
WHERE [Dupe].[Student ID] = [Spanish Course History].[Student ID]
ORDER BY [Dupe].[Calendar Year] DESC, [Dupe].[Calendar Month] DESC,[Dupe].[Last Name] DESC, [First Name] DESC, [Dupe].[Student ID] DESC)));

 

[rmcafee]

I believe because of the way the data is stored it would be much easier to pull the last 4 most recent dates. That might get rid of most of my problems, but my administration asked for only the last three. Going to keep working on it.