Query to find gaps in dates

[Ray]

I work at an alternative school that has rolling enrollment, and a student
may come and go several times in a year. I have a table that tracks student
attendance. The date that students are dropped from enrollment is encoded in
the table and easy to find with a simple query, but the date that they
re-enroll is not marked in any special way because they are only required to
register once a year. I have no control over the underlying table, so I
can't change any of that.

I am trying to set up a query that will give me the next date attended after
each drop date, so that I can figure out the size of the gap in their
attendance (that is, the number of school days that elapse between when they
left and when they come back). The closest I came was this:

SELECT DatesAttended.StudentID, qDropDate.Last_Name, qDropDate.First_Name,
qDropDate.DropDate, Min(SchoolDays.Date) AS FirstDate,
Min(DatesAttended.Date) AS LastDate
FROM SchoolDays, qDropDate INNER JOIN DatesAttended ON qDropDate.StudentID =
DatesAttended.StudentID
WHERE (((SchoolDays.Date)>[qDropDate].[DropDate]) AND
((DatesAttended.Date)>[qDropDate].[DropDate]))
GROUP BY DatesAttended.StudentID, qDropDate.Last_Name, qDropDate.First_Name,
qDropDate.DropDate
ORDER BY qDropDate.Last_Name, qDropDate.First_Name;



But that only works if they only have one drop date. If they leave and come
back twice or more, this query gives the same re-enroll date for both drops
rather than the first, distinct date attended after each different drop date.


I hope this makes sense and somebody will not only understand what I am
trying to do but will also be able to help me do it.