I have a table ATTENDANCE that imports records from the MEMBERS table each time an event for which attendance is recorded occurs.
This table is not fully normalized as the rest of the database is because members become inactive at times and are moved to the INACTIVE_MEMBERS table. When that occurs, I do not want their attendance records to become unrelated to the ATTENDANCE table. I want to keep permanent attendance records and counts even if the member(s) become inactive.
I am trying to create a report layout that lists members who have 2 consecutive absences. Then, I'll create a report for those with 3 consecutive absences, and then 4 or more consecutive absences.
I created a report that shows the members name in a sub-summary section printed above and in the body section, it lists the dated for which the member was marked absent. I have created a summary field which counts the primary key of each record in the ATTENDANCE table and that is displayed in the sub-summary section next to the members name so that the report is something like this.
LAST NAME, FIRST NAME COUNT OF ABSENCES
Members with no absence dates are not on the report after I perform a find on the field presence in the table ATTENDANCE for records that equal "absent."
What I am unaware of how to do is:
1.) Define a range of dates to be included in the report
2.) Eliminate members from appearing on the report if they have only 1 absence within the date range or they have non-consecutive absences within the date range (the very date range that I cannot define.)
Please help if you can.