How to design an attendance table?

[Vanga Sasidhar]

Hi all,

I am designing a Database in Microsoft Access 2003 for a project. Here i
got a problem for the table desinging for an attendance register.

This is for a vacational training institute. people can join for
different courses at any day of the month. everyday the administrator will
take the attendance for each student. i have already given "Application
Number" as the primary key. Now how can i desing a seperate table for
storing attendance of these students.

The conditions are ...

New students can join on any day. From the date of joining his/her name
will be included in this attendance table. Do i need to maintain a seperate
table for each month? Do i need to create a seperate table at the starting
of every month? If i create a seperate table for each month what about the
students? These students number will be increased on different dates? how
can i keep track of this also.

I suppose i am able to explain my problem completely. If it need any
further information or clarification, please let me know about it. I will
provide that info also.

With Regards,
Vanga Sasidhar

 

[John Vinson]

Hi all,

I am designing a Database in Microsoft Access 2003 for a project. Here i
got a problem for the table desinging for an attendance register.

This is for a vacational training institute. people can join for
different courses at any day of the month. everyday the administrator will
take the attendance for each student. i have already given "Application
Number" as the primary key. Now how can i desing a seperate table for
storing attendance of these students.

So the Application Number is a unique identifier for each student?
That's ok...

The conditions are ...

New students can join on any day. From the date of joining his/her name
will be included in this attendance table. Do i need to maintain a seperate
table for each month? Do i need to create a seperate table at the starting
of every month? If i create a seperate table for each month what about the
students? These students number will be increased on different dates? how
can i keep track of this also.

No. Separate tables are emphatically NOT the way to go. I would
suggest an Enrollment table with fields [Application Number] linked to
the Student table; [CourseID] linked to the table of courses;
DateEnrolled; DateCompleted; and any other information you need to
record about this student in this course.

The Attendence table would have fields [Application Number],
[CourseID], and [AttendenceDate]. The Form used to fill this in would
have a combo box based on the a query based on the [Enrollment] table
to present the list of currently enrolled students; the query might be

SELECT [Students].[Application Number], [LastName] & ", " &
[FirstName]
FROM [Enrollment] INNER JOIN [Students]
ON [Students].[Application Number] = [Enrollment].[Application Number]
WHERE [Enrollment].[DateEnrolled] <= Date()
AND ([Enrollment].[DateCompleted] >= Date() OR
[Enrollment].[DateCompleted] IS NULL)
ORDER BY LastName, FirstName;

with appropriate changes for your table and fieldnames of course.

John W. Vinson[MVP]