This table design has me stumped

[Guest]

I'm building an App to track student progress. I'll need a student table but
the progress tracking has me stumped. There are 22 courses. Each student will
be enrolled in only 5-6 courses. Each course is comprised of 5 Modules, each
module could have 5-10 assignments. They will track each assignment using D
for doing and F for finished.

All I can think of is 1 table for each course but I think trying to
query/report across 22 tables would be messy.

The end result is the user can select a student maybe check the courses they
are in. Then later with another form they can select the student, view the
courses and change the assignment code as needed. I'm having a hard time
getting my mind wrapped around the 40-50 assignments per course.

Ideas?

 

[Guest]

No expert but I only see 5 tables

Students
Courses
Modules
Assignments
Grades

Then put in the various links - then one form with a variety of subforms

 

[Allen Browne]

The most basic structure would include these tables:

Course table (one record for each course, with CourseID primary key)

Module table (one record for each module in a course.) Fields:
ModuleID primary key
CourseID foreign key to Course.CourseID

Assign table (one record for each assessment in a module.) Fields:
AssignID primary key
ModuleID foreign key to Module.ModuleID

Student table (one record for each student, with StudentID primary key)

Enrol table, with fields:
CourseID foreign key to Course.CourseID
StudentID foreign key to Student.StudentID

Submit table (one record for each assessment submitted.) Fields:
AssignID foreign key to Assign.AssignID
StudentID foreigh key to Student.StudentID

This assumes that a course consists of one or more modules. (If a module can
count towards more than one course, you need another CourseModule table.)
Similarly, an assignment is for one module only. So with the first 3 tables,
you have defined the courses, their modules and their assignments.

When a student enrols in a course (the enrol table), we've assumed they
must do all assessments as they tackle each module. (If you have elective
modules, or students can choose between assignments, things are more
complex.) So now you now what assessments students must complete.

If the course modules are offered at specific times (e.g. with lectures), or
if the assessments are due at specific times, again, you need more tables to
handle the multiple instances when subjects are offered, and the assessments
specific to these instances (since they may change over time.)

When a student submits an assessment, you enter it in the submit table.
There will probably be other tables defining the grade a student achieved
(or at least whether they are competent or not), and you may need to handle
resubmissions (where a student is not competent the first time.)

 

[Guest]

Allen,
I've created the structure as you explained it and it seems to make sense.
I'm going to mock-up a few forms around it and see if I can get it to work

Thanks

Todd

 

[Guest]

I'm using Access 2007.

I created the tables as recommended and worked-up some forms. One form was
for linking the student to the course using the Student table as the master
and the course table as a subform. (The query behind it also brings in the
Enroll fields) In the subform I have enroll.checkbox to indicate the linking.

I test this by opening the form select a student see the course titles and
check a few boxes to indicated they are enrolled then close the form.
Examining the enroll table reveals a check in the box but neither the student
ID nor the Course ID are inserted. Ideas?

 

[Allen Browne]

To enrol students in a course, you need a form with a subform.

The main from is bound to the Course table.
The subform is bound to the Enrol table.
The subform has a combo box where you select the student who is being
enrolled in the course. Access will assign the course from the main form to
the subform, so you don't need to show a text box in the subform for the
CourseID (though it is a good idea to have a hidden one there.)

You don't use check boxes. The subform only has a record for each student
enrolled in that course.