Need help creating a report with a variable amount of fields
I have put together a database to help me keep track of class attendance for the classes I teach.
At my school there are 10 homeroom classes which take the same lessons together every period. I teach each homeroom class twice a week, however due to different school events, classes are cancelled and not every homeroom class takes the same amount of lessons. In my current database I only track absences and a present status is implicit.
What I would like to do is create a report that lists all students in a homeroom class, all classes that homeroom class has had, and whether a student was absent for that class.
Ideally I'd like to have students listed down the vertical axis, classes taken listed along the horizontal axis (which is variable), and the intersection of the 2 showing whether the student was absent or not.
I currently have the following tables
HRClass (holds info about each homeroom class. Id, grade, class number, teacher, size)
Student (pretty self explinatory. ID, student id (given by school), student grade, student hrclass, student name)
Course (mostly for extensiblilty. ID, course name)
ClassDetails (id, course id, date, period, grade, hrclass, description)
Attendance (id, ClassDetails id, student id, absence type)