Classroom attendance - a bit lost

[thaonguyen]

Hi everyone,

So I am making an access database to check attendance of our Accountancy classes. I have
- tblClasses: with information of ClassID (PK) day of the week, morning/ afternoon, tutor, classID, level (3 or 4)
- tblLearners: with information of all our learners LearnerID (PK), fullname, employer, classID (not all take classes at our venue, some do online), Level
- tblJntLearnersClasses with ClassID and LearnersID of those in that class, Classtimes
- tblClassAttendance (currently blank) but with the following fields ClassAttendanceID (PK), ClassID, LearnerID, three Yes/No fields (InClass, AuthorisedAbsent, Late) and a shorttext field IfLateHowLong
- tblAttendance with AttendanceID PK and Attendance (Present, Authorised Absent, Unauthorised Absent, Late)

so I want to be able to have a form where:
- Combobox to pick a class (Dayofweek, Level, Morning/afternoon)
- Then in a subform(???) it shows the list of the learners and their employers with the combobox to pick the Attendance Status and the IfLateHowLong text
- In the footer of the subform there is a Date text box where the tutor will choose the date, then they can check attendance (which the info then is recorded in the tblClassAttendance table)
- There is a button to refresh and a message said "Done"

So I have managed to find 2 tutorials that
- One shows how the subform on_focus and change information based on the ClassCombox.
However, as I want to include the Attendance combobox and the textfields, I don't know how to link everything up.
- One tutorial shows how to use the Append query to check attendance of a class. However, as we have many classes, I am just stumped.

Also I want to be able to add new learner(s) from the tblLearners to the Classlist. I assume I would need an Update or Append query again.

Can you please point me to the right direction or if you have a similar database that I can take a look and make necessary changes, that'll be super helpful.

I am not super familiar with Access but I can play around and make things happen if there are similar databases.

Thanks so much

 

[theDBguy]

Hi. It might be easier to help if you could post a sample copy of your db. Just a thought...

 

[Pat Hartman]

Also I want to be able to add new learner(s) from the tblLearners to the Classlist. I assume I would need an Update or Append query again.

No. Adding records is done via bound forms. You can do this from two directions. Either from the direction of the learner or from the direction of the class. So, option one is to use a main form bound to the learners table and a subform that shows the classes for that learner. To add a new classs, you tab to a new row in the subform, choose the class from a combo and put in any other relevant information such as morning/afternoon/evening, start date, etc. Technically no code is required to add the record. However, as you gain experience you will understand why you want to perform validation in the form or suborm's BeforeUpdate event to ensure that only valid records get added. For example, it makes no sense to allow a class record to be added in this scenario if you have not picked a class. That's what validation will prevent.

The other view of the data (and you can actually have and use both) is to use Class as the main form and learner as the subform.

What I just described is enrollment. Attendance is actually a different process. Attendance is who attended a class on a particular day. So, if the class will meet 10 times in total, you will add a record for each meeting for each person who was present. At the end, you can tell that Sally attended all 10 class meetings but Joe only attended 7. Maybe that disqualifies Joe from getting a certificate.