Do you (or might you in the future) have multiple rosters of students who "Attend one class per week"?
If more than roster, is it possible that the same student attends more than one class?
These are key design questions that should be answered before setting up a database as they can dictate either a very simple structure (only one roster of students both now and in the forseeable future) or with certain level of sophistication (to handle cases where one student attends more than one of your classes.)
What will you be doing with the attendance data after you've recorded this data? (Some ways to set this up make for easy layout setup, but more troublesome reports, say to summarize a student's attendance record.)
Hi, sorry for the delayed response...
Currently we run 6 different classes, each occuring once per week. Most students attend only one class, although a handful attend more than one of these classes. We anticipate that this number will increase as more students choose to attend additional classes.
At this time, I would use the data in the register layout format to see each class attendance in a list/table layout, and also perhaps viewing each student's attendance in their individual Student Record Card.
Here's the minimum tables, I'd use for this. Likely more tables than this will be needed in the final solution:
Students (one record for each student enrolled in at least one class)
Student ID ( Serial number)
Other fields on student as needed.
Classes ( one record for each class you offer.)
ClassID (Serial number)
Other fields about a class as needed such as the date/time it meets.
Enrollment (One record matching a student to a class they have enrolled in.
Attendance (One record for each student attending a specific class on a specific date)
Date (Date, date of class meeting)
AttendanceStatus (a field to record the student as present, absent, late...)
Other tables you may want/need:
Subjects (If you have one subject taught in multiple sessions)
Schedule (Documents each meeting date for a given class session)
Keeping to the basic tables, you'd have these relationships:
Students-----<Enrollment>---Classes (---< means one to many )
Students----<Attendance>----Classes (you'll need a second table occurrence of classes)
You can then set up a portal to Attendance on your Classes layout, filtered by date to show just the attendance records for that class, for that meeting date. Student name fields from Students can be placed inside this portal. To create the records in Attendance, you'd copy the matching records from Enrollment, but date them for each meeting date. This can be done with a script.
One reason you might use a Schedule table, is to better be able to manage the attendance records by placing a portal to Attendance on a Schedule layout and use a relationship that matches both classID and meeting date to show the correct records.
Thanks! That's a great start, however I don't quite understand how to configure the relationships between these tables. Could you elaborate a little more?
I am trying to create something similar. Courtesy of sorbsbuster I have got a database that allows me to call up a teacher's classes & from there go to a list of all the students in a particular class. This part makes my role as the Head of the Science Department easier. My next step is to be able to mark the attendance of students in my classes.
The above (the attendance part) would only be for my personal use only i.e I would be the only one using this database & I am only interested in marking the attendance of the students in my classes (4 classes with about 25 students each). The students in my classes would have a class (Science) on most days & there is no point in considering any other classes (English, Maths, after school tutorials, etc.) they attend.
What I would like to see (when I pull up the attendance table) is something like the following:
<– Monday 14th June –>
The "present" could be a tickbox or radio button. The "Late" & "Note" parts would allow me to enter text and finally, the date at the bottom would allow me to obviously see the day's date but also allow me to scroll through previous or future dates.
My Database originally had similar organization to the one posted by PhilModJunk. I have added the necessary tables and all of the records but I am stuck on how to copy the records from Enrollment and add class meeting dates to create the Attendance records using a script. Despite reading a lot of the stuff about scripts and messing about trying to make one to do this, I still can't figure out how do this. Any advice on how to write a script to do this is greatly appreciated. Thank you.
To create the records in Attendance, you'd copy the matching records from Enrollment, but date them for each meeting date. This can be done with a script.