Attendance Table

[DUNNER7]

I am trying to design a student/faculty attendance table for my information
database for a school I run. I would like to track the attendance for about
300 students and 50 faculty. This would be a daily event and the default
value would be "present". My question is how could i desgin a form/table to
enter this data on a daily basis and record it under a specific date? Would
I need a table for each date (which would make about 182 of them) or is there
a way to share this data with an excel table? I think if someone talked
about this with me or gave me a direction to start that I would be able to
get started.

Thanks
Del Dobbs

 

[Michael Gramelspacher]

I am trying to design a student/faculty attendance table for my information
database for a school I run. I would like to track the attendance for about
300 students and 50 faculty. This would be a daily event and the default
value would be "present". My question is how could i desgin a form/table to
enter this data on a daily basis and record it under a specific date? Would
I need a table for each date (which would make about 182 of them) or is there
a way to share this data with an excel table? I think if someone talked
about this with me or gave me a direction to start that I would be able to
get started.

Thanks
Del Dobbs

Well, you could have a SchoolCalendar table with a date for each school day or a calendar table with
a date column and another column for IsSchoolDay. There could be other columns for year_num,
month_num and semester_num

You would need a table for StudentAttendance with columns for calendar_date, student_id and
attendance_code

INSERT INTO StudentAttendance ( student_id, calendar_date )
SELECT Students.student_id, SchoolCalendar.calendar_date
FROM Students, SchoolCalendar
WHERE SchoolCalendar.IsSchoolDay = -1 and SchoolCalendar.year_num = 2008
and SchoolCalendar.semester_num = 1;

Query not tested.

You now have an attendance roster for every student for every school day in the semester.

You could make a form based on StudentAttendance and have a calendar control in the heading to
select the date to work with. Change the forms recordsource to only show this date's attendance.

You would also need queries to add new students to StudentAttendance and delete withdrawn students
from StudentAttendance.

Just my ideas, maybe somone else has another suggestion.

 

[Beetle]

I don't know all of your circumstances, but here is something to consider.
Don't keep track of what you don't need to know. In other words, do you
really need to keep track of who was present each day? A person is either
absent or present, and since the vast majority of students/teachers will be
present each day (presumably), then it is a lot easier to just keep track of
who was absent. You might have tables like;

tblPersons
********
PersonID (PK)
FirstName
LastName
PersonType (student or teacher)
other fields related specifically to each person

tblAbsences
*********
AbsenceID (PK)
AbsenceDate
PersonID (FK to tblPersons)
AbsenceReason

 

[Michael Gramelspacher]

I don't know all of your circumstances, but here is something to consider.
Don't keep track of what you don't need to know. In other words, do you
really need to keep track of who was present each day? A person is either
absent or present, and since the vast majority of students/teachers will be
present each day (presumably), then it is a lot easier to just keep track of
who was absent. You might have tables like;

This is all right. It seems though that some people have a need to check off attendance against a
roster. It is just a matter of how the person wants to do it. With substitute teachers it helps.
An actual list is definitive. The person is on the list and not marked absent. The other way the
presence is assumed by the lack of an absence entry. I guess most people would not recognize a
distinction.

 

[Beetle]

Apologies Michael, I mistakenly replied to your post instead of the OP's.
I see your point though, that the OP may have reasons for needing to check
off a complete roster each day.

 

[Klatuu]

This is a very normal situation. The Student Attendance model is even often
used in teaching relational database design because it is one step above
simple in its complexity.

The typical model is:

Student Table - demographics on each student

Facutly Table - demographics on each faculty member

Course Table - Identifies all the courses offered. Not the specific
occurance of a course, but a definition of the course including prerequisits,
etc.

Registrations Table - This is an occurance of a Course, It defines the
place, time, and Faculty who will be teaching. It may also include how many
students may register.

Attendance Table - This keeps track of which student attended which Class on
what date. Where Class means a record in the Registrations table.

 

[DUNNER7]

Thanks, you guys all put it in simple terms...I was overthinking the design.
However when designing the "attendance table" I would imaginge that the
default value would be "present" and this way someone could go down and put a
check in a box if the student was absent. My question is more towards the
storage of the data. Would I design a table with 182 columns by date? That
seems pretty cumbersome. Thanks for your continued assistance