Counting more specifically
I have a school database which tracks general student stuff like which classes they are in, attendance, etc.
I'm having trouble calculating the number of 'Present', 'Late' and 'Absence' occurances by student and date.
I can count the total number of 'Present' etc. occurances for each class but can't get my head around refining it to only counting 'Present' for Student A
For this section of the solution I'm using 3 tables
'Students', 'Attendance_Lines', and 'PresentAbsentLate'
'PresentAbsentLate' just contains 3 records "Present", "Absent" and "Late" so I can relate that to the 'Attendance_Lines' table.
The 'Attendance_Lines' table relates to 'Students' and has the students name, attendance, date and class.
I can count the total number of students in a class via the relation to the 'PresentAbsentLate' table but how do I then count the number of "Present" entries for just StudentA in the 'Attendance_Lines' table and then also the number of "Present" entries for each class over a given date range.
I want this calculation to appear in real time on the 'Students' layout as the user scrolls through the names of Students, their attendance shows up, as over all, last 2 weeks, last month etc. I don't want to do a find every time as it seems like that would slow everything down when the 'Attendance_Line' table gets huge.