Entering the attendance of the whole class with a default value automatically

[hassan.khalifeh10@outlook]

I have a class which includes several student. I need to save their attendance in a specific date. I need to auto fill the attendance of all the students automatically, where the attendance state is selected as a foreign key, and a default value is selected. If we need to change a specifuc attendance state, we can edit then save tge table.
I solved my case by entering the attendance state for each student individually.
Kindly, what are the steps?

 

[June7]

Could execute an INSERT INTO SELECT action SQL to batch insert a group of records. Something like:

CurrentDb.Execute "INSERT INTO Attendance(AttDate, StudentID, AttCode) SELECT Date(), StudentID, 1 FROM Students"

If you need more assistance, provide specifics about data structure. Could even attach file. Follow instructions at bottom of my post.

 

[June7]

Or use INSERT INTO SELECT to exclude students that already have records for specified date. Use appropriate filter criteria in the SELECT subquery, likely involve an IN() clause. Or an "UPSERT" query https://stackoverflow.com/questions/6199417/upserting-in-microsoft-access. No looping.

 

[MarkK]

I would not record both presence AND absence. It is a boolean value, and therefore impossible in the real world that both might exist. Thus, make it impossible in your model as follows-->assume one, and record the other.

Create a tAbsent table that stores PK, StudentFK and Date, and you are done. Manage the absence data only. If there is no record in tAbsent for a student on a date, the student was not absent.

 

[MarkK]

So, it really depends on the ultimate goal of the recorded information.

If what matters to your goal is attendance, not absence, then only track attendance. If there is no attendance record, the student did not attend.

To query this data, use an outer join. If the linked table row ID is null, a row does not exist, and, depending on what angle you came at it from, the student did or did not attend.

But entering your actual state data, and then running an after-the-fact update query to fill in the inverse booleans for select dates, or pre-populating state data you can assume to be true, and then editing that to show select falsifications, seems like a make-work project. Just record the facts. If a fact does not exist, do not record it.

Also, if you store both, then your table must allow presence and absence on the same day. You could fix that with unique index, but your table can never detect the case where both presence and absence are missing. So now you need pre-population, an index, and after-the-fact validation. These are not the hallmarks of an elegant solution.

 

[MarkK]

.