Table Design for Recording School Grades

[Steve Freides]

I'm designing a database to track attendance and grades for me and my wife -
we recently both started teaching at a local community college. I'm new to
Access but have designed many databases and tables before. Please tell me
if I'm going about this the wrong way and how to get around the problem I'm
having.

I'm planning on 3 tables: one to hold semester, faculty name, class number,
and student name. I'd like to have an autoincrementing ID number for that
table. Right now, it would have one record for each class me and my wife
are teaching.

Table #2 would hold attendance and table #3 would hold grades. Each would
relate back to the first table based on the autoincrementing ID in that
table. I don't know what data type to assign the field in the attendance
and grades tables that would hold the value of the corresponding table_1 ID.

Many thanks in advance.

-S-

 

[Allen Browne]

For a foreign key field that relates back to an AutoNumber, use a Number of
size Long Integer.

The basic table structure you need would be something like this:

1. Student table: one record for each student.

2. Faculty: one record for each teacher.

3. Subject: one record for each type of class that can be taught (e.g.
"Computer Awareness 101"). If desired, the subjects might also have a
foreign key indicating the department this subject comes under (e.g. IT or
Math department).

4. Class: one record for each instance of a subject (e.g. "Computer
Awareness 101" in 2003 Semeseter 2, taught by Fred Jones).

5. Enrolment: one record for each Student in a Class.

6. Attendance: one record for each combination of student+date+class.

7. Assessment: one record for each assessment you assign for a class,
including the weightings of the various assignments. (These tie to the class
rather than the subject, as your assessments will change over time.)

8. Result: one record for each combination of student+assessment.

 

[Steve Freides]

For a foreign key field that relates back to an AutoNumber, use a Number of
size Long Integer.

Thank you very much - that seems to have done the trick.

The basic table structure you need would be something like this:

-snip-

I will play around with the table structure as I continue to work on this
and post in a new thread with any further questions.

Thanks again.

-S-
http://www.kbnj.com