Please help with Student Attendance Register Database

[Patrick]

I would like some help with table design and relationships for a
database that records student attendance on a class by class basis.

What I would like to do is store attendance information for students
in my classes. I would like to be able to do this with a form which
displays the students names in a list for a particular class and a
checkbox for present/absent.

What is the best structure for the tables and what should the
relationships be and how can I make a checkbox input date information
into a particular record field?

I can create a form which calls up a particular class list in a
subform in datasheet view by using a combo box but I am stumped on the
checkbox bit and I am not sure what tables and relationships I should
have to record and store the attendance data.

My current design is:

tbl_Students
------------------
Admission No (Primary Key)
Name
Forename
Class
------------------

tbl_Attendance
------------------
Attendance No (AutoNumber Primary Key)
Admission No (Foreign Key)
Date
------------------

I am sure I have got this wrong because the second table would end up
being huge but how best to store the data?

I would be grateful for any suggestions/pointers.

Patrick

 

[Tim Ferguson]

I would like some help with table design and relationships for a
database that records student attendance on a class by class basis. ....

tbl_Attendance

Even with the redundant AttendanceNo (please don't put spaces in field
names...) it's only three columns, so that is no way a huge table. It will
end up with a lot of rows, to be sure, but that is what dbms's do best so
don't worry about that. On the subject of field names, don't use "Date"
either, because it clashes with the vba function, sql reserved word and the
Access statement, and you are likely to get yourself well confused.

An additional point is that you may want to store something about the
attendance on a given date: for example Present, Unauthorised absence,
Sick, Vacation, school Closed and so on. I would suggest a Text(1) column
to store what what happened on the day, with a ValidationRule to keep it
restricted.

Hope that helps


Tim F

 

[Patrick]

Even with the redundant AttendanceNo (please don't put spaces in field
names...) it's only three columns, so that is no way a huge table. It will
end up with a lot of rows, to be sure, but that is what dbms's do best so
don't worry about that. On the subject of field names, don't use "Date"
either, because it clashes with the vba function, sql reserved word and the
Access statement, and you are likely to get yourself well confused.

An additional point is that you may want to store something about the
attendance on a given date: for example Present, Unauthorised absence,
Sick, Vacation, school Closed and so on. I would suggest a Text(1) column
to store what what happened on the day, with a ValidationRule to keep it
restricted.

Hope that helps


Tim F

Thanks forthe advice, Tim but I'm still not sure about whaich tables
to have and what the relationship between them should be. Furthermore
I am stuck on how to present my data in a form that mimicks a class
register. Any ideas?

Patrick