How can I search a date field (Date Lesson) in a table (Attendance) to see if there are 3 consecutive records that have the date field occur within a 3 week (21 days) period and their attendance is marked as '1' in another table?
I am developing something where attendance is recorded and when a person attends 3 consecutive Sunday's they are upgraded to full membership. Currently I am using SQL to do this and it works if I provide a date to initiate the process. For example, if today is Sunday I can provide today's date and check to see if the person was in attendance the previous 2 Sundays and if yes I upgrade them to member.
SELECT a."_fkMember" FROM Attendance a INNER JOIN Lesson b ON a."_fkLesson" = b."_pkLesson" WHERE a."Present" = 1 AND b."Date Lesson" BETWEEN ? AND ? GROUP BY a."_fkMember" HAVING COUNT(a."_fkMember") = 3
What I would like to do if possible is replace the SQL with a combination of Calculation and Summary fields if possible. So without providing a starting date for the comparison (preferably, but a solution using the current date would get me started) how can I search the "Date Lesson" field in the Attendance table to see if a person has 3 consecutive appearances (records in the Attendance table where the Member table's field Present = 1)?