Returning the previous enrollment date for a client?

[loopa1]

Hi all

I have a table which contains over 5000 records. One student can be present many times and they have a unique ID. Each row relates to a student and contains the date of when they enrolled on a specific course.

How do I query the table so I return the second most recent enrollment date? So if a client has enrolled 5 times, with the 5th being the most recent, I want the date of the 4th. Similarly, if the client has enrolled 30 times, I'd want the date of the 29th time.

The enrollment date field is sorted in ascending order (if that matters).

I'm thinking of some combination of 'Large', 'Index' and 'Rank' but I have no idea where to start or if these are in fact Excel only functions.

Thanks.

 

[jackd]

You might get some ideas from this link and this section(Get the value in another record)

http://allenbrowne.com/subquery-01.html

 

[jasmith4]

You need two queries, one to get the max (most recent) of each student's dates, and another one which uses the first one's results in the criteria that will say "less than the max date".

Assume your table is called "StudentsDates", and the relevant fields are called "Student" and "Date".

Here's the SQL for the max-date query, called "StudentsMaxDates":

SELECT StudentsDates.Student, Max(StudentsDates.Date) AS MaxOfDate
FROM StudentsDates
GROUP BY StudentsDates.Student;

Here's the SQL for the 2nd-max query:

SELECT StudentsDates.Student, Max(StudentsDates.Date) AS MaxOfDate1
FROM StudentsDates
INNER JOIN StudentsMaxDates
ON StudentsDates.Student = StudentsMaxDates.Student
WHERE (((StudentsDates.Date)<[StudentsMaxDates].[MaxOfDate]))
GROUP BY StudentsDates.Student;

Once you put in the SQL (in View/SQL), you should view the Design mode to see how each one was done.