query:list records of 3 or more consecutive days

A

alan.y

I have a very large database of attendances.
I want to list records where people have attended on three or more
consecutive days (Field = AttendanceDate).
can anyone point me in the right direction?
Many Thanks
Alany
 
B

Brendan Reynolds

SELECT Attendance.*
FROM Attendance
WHERE ((((SELECT AttendeeID FROM Attendance AS a WHERE a.AttendeeID =
Attendance.AttendeeID AND a.AttendanceDate = Attendance.AttendanceDate - 1))
Is Not Null) AND (((SELECT AttendeeID FROM Attendance AS a WHERE
a.AttendeeID = Attendance.AttendeeID AND a.AttendanceDate =
Attendance.AttendanceDate + 1)) Is Not Null));

The two sub-queries here are selecting records where the same attendee
attended the previous day, and the next day, respectively. If both
sub-queries return non-Null values, the attendee attended on three
consecutive days.
 
Top