B
BJM
I have a database in development that is intended to keep
student attendance records for our local self-reliant
continuing education department.
The trouble is, since each teacher has the potential to
teach more than one course, each student to take more than
one course (and, as our program is one of continuous
enrolment, not necessarily in tandom with each other), and
even each course to have more than one teacher, the
database is plagued by many to many relationships.
The only way I have found to solve this problem is by
storing the attendance records in an attendance table
related to each of a student, teacher, and course table
(by way of an enrolment join table), with each record in
the attendance table including student ID, course ID, a
week number field, and, finally, a field to contain the
number of hours that week the student spend working on
that particular course.
My boss is looking for instances of ongoing, unexplained
non-attendance. He would like a weekly report that
contains the names of students with no attendance for the
previous three week period.
A query currently prompts the data entry person for an
opening week number and then a closing week number in
order to create the report.
Is there a way I can capture the pair of week numbers and
have them display in a report control as dates? For
instance can I code week one to have a value of September
7th, 2003, and have the control calculate dates based on
subsequent week numbers from there?
The only alternative is to have the data entry clerk type
the dates countless times each week into what is now the
week number field. It would be far easier to just type a
week number. With 900 plus students, this could easily
amount to thousands of records a week!!!
I know the best way would be to have the teachers enter
the information electronically into a form and thus
populate the underlying table without need for data entry,
but there are non-technical folks on staff that this
prospect would not work well with!
Thanks,
BJM
student attendance records for our local self-reliant
continuing education department.
The trouble is, since each teacher has the potential to
teach more than one course, each student to take more than
one course (and, as our program is one of continuous
enrolment, not necessarily in tandom with each other), and
even each course to have more than one teacher, the
database is plagued by many to many relationships.
The only way I have found to solve this problem is by
storing the attendance records in an attendance table
related to each of a student, teacher, and course table
(by way of an enrolment join table), with each record in
the attendance table including student ID, course ID, a
week number field, and, finally, a field to contain the
number of hours that week the student spend working on
that particular course.
My boss is looking for instances of ongoing, unexplained
non-attendance. He would like a weekly report that
contains the names of students with no attendance for the
previous three week period.
A query currently prompts the data entry person for an
opening week number and then a closing week number in
order to create the report.
Is there a way I can capture the pair of week numbers and
have them display in a report control as dates? For
instance can I code week one to have a value of September
7th, 2003, and have the control calculate dates based on
subsequent week numbers from there?
The only alternative is to have the data entry clerk type
the dates countless times each week into what is now the
week number field. It would be far easier to just type a
week number. With 900 plus students, this could easily
amount to thousands of records a week!!!
I know the best way would be to have the teachers enter
the information electronically into a form and thus
populate the underlying table without need for data entry,
but there are non-technical folks on staff that this
prospect would not work well with!
Thanks,
BJM