I know that questions about tracking attendance have come up in the past, not always with a good solution. Here is my version of the Attendance Tracking question.
I have a Guests table with directory information for guests at a homeless shelter (name, dob, gender, date of first contact, date of last contact, etc.)
Nightly attendance is tracked in another table called attTable. This table has only a few fields: ID, NightOf (date), Guest (link to Guests table), Attendance (single select field with options like Present, No show, Absent-Called, etc), and Comment.
I can do a simple lookup of a particular guest in the attTable to get their entire attendance history. I can show rows of attTable for a given night to show who was at the shelter that night. This design works well.
The problem is that 60 guests per night times 365 nights per year equals 21,900 table rows per year. This means that my Pro account (50,000 rows per base) is severely limited to tracking only 2 years worth of attendance data at a time.
This is a simple, flexible db design that can easily show the data organized and grouped the way we need it. But, it takes a lot of rows to do it. Is there something I’m overlooking in the design? Is there a good way to track this data without running up against the rows per base limit so quickly? Any insights would be greatly appreciated.
john