Another Student Attendance Question

[SusanCB]

Yes, another dreaded Student Attendance Database question. Sorry to have to ask, but I can’t seem to find a solution to my particular problem.

I’ve got a training tracking database. The relevant tables are:

People (contains StudentID, FirstName, LastName, etc.)
Training (contains CourseID)
CourseDate (contains CourseID, CourseDate, etc.)
Enrollment (contains StudentID, CourseID, etc.)
Attendance (contains StudentID, CourseID, CourseDate, and Attended – a Yes/No checkbox)

The course dates are set. The Enrollment table holds the info on all the students enrolled in the course. I’m trying to take the names from the Enrollment table and create a form that will let instructors tick off the attendance box for each student for each date, and add this info to the Attendance table.

Ideally, the form would look something like the attached table.


Can anyone help? Obviously I’m a relative Access novice, and I don’t know jack about VBA, so any advice you could give would be fantastic. Thanks so much.

 

[SteveH2508]

People (contains StudentID, FirstName, LastName, etc.)
Training (contains CourseID)
CourseDate (contains CourseID, CourseDate, etc.)
Enrollment (contains StudentID, CourseID, etc.)
Attendance (contains StudentID, CourseID, CourseDate, and Attended – a Yes/No checkbox)

I am a bit confused by your table design. I don't understand the distinction between the Training and CourseDate tables.

The CourseDate field is odd. Either it is redundant data or is the CourseDate in the Attendance table actually a LessonDate?

The only way you will be able to create a grid-like register form as per your attachment is to create an unbound form on-the-fly and use VBA to write the values to your table - NOT TRIVIAL. It is possible to create a printed report like that using a crosstab query - again not a trivial exercise. (I know I have built just such a system in the past).

A much better way is to have a main form for the Lesson and a sub-form with the Attendances on it.

You could also have a Student main form and an Attendance sub-form to enter the data another way.

I think you need to address your table structure before you get too deep into forms and reports.

 

[SusanCB]

Thanks for the speedy reply, Steve. By CourseDate I mean the date of each lesson. I originally had fields in the Training table called CourseDate1, CourseDate2, etc. but I got scolded in this forum for not having normalized my data. Hence the restructured tables.

I could easily create a form for each lesson date (Course Date) with an attendance subform, but the instructors are balking at having to go into six separate forms for six lesson dates. Same for having to go to each student's form to enter the attendance data.

 

[SteveH2508]

With a bit of VBA coding you can set up the Attendance subform to click one button and mark all as attended - then you go through and remove the absentees. No biggy there. Your instructors are only going to have to go into one form - they may access several Lesson records in that one form.

See attached