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.

 

[Pat Hartman]

I would first manually add the absent records. Then run an append query to append the present records for everyone else. That way you never add invalid data to the database to begin with. I would use a multi-select list box to select the absent records and run a loop to append those records.

 

[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.

 

[Pat Hartman]

Thus, make it impossible in your model as follows-->assume one, and record the other.

The only way that works is if the population for the class is always 100% the same from first meeting to last. If a student can drop the class or be added after the first meeting, then you have to use the student's enrollment date and drop date in any query in order to figure out who attended and who didn't for any class. I'm pretty sure that's why all models that I've ever seen use the current enrollment to find the universe of students and mark each one.

If you mark absence only, that would be the simplest strategy and the absences are the values that can't be assumed. But for statistical purposes, you may still have difficulty without having the set of enrolled students at the time of each class to work with. So, it really depends on the ultimate goal of the recorded information.

 

[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.

 

[Pat Hartman]

If you have 50 students, would you rather enter 48 records or 2? There is NO way a query can automagically devine who was present and who was absent so you must enter one or the other. Two seems to be the most efficient. Then if you want both status', use a query to append the present records. Appending all records and remembering to update the remainder seems to be the most likely way to generate errors. At least if you want all records, you have an easy query that will tell you if all rows are entered. There is no way to identify bad data if you start by appending a row for each enrolled student, planning and plan on "fixing" the data later.

So, use a multi-select listbox to select the absent students. Then your button, appends the absent rows and runs a second query to append the present rows. With the whole thing done in one procedure, once it works, it works and you don't have to worry about missing data at least.

 

[Pat Hartman]

Also, if you store both, then your table must allow presence and absence on the same day.

Why? For a class, you are present or absent. You could add other status values if there was something else you wanted to track such as "late" or "left early" Both would imply present and have to be counted with "present".

 

[MarkK]

.