If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
This table design has me stumped
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? |
#2
|
|||
|
|||
This table design has me stumped
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 "TLuebke" wrote: 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? |
#3
|
|||
|
|||
This table design has me stumped
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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TLuebke" wrote in message ... 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? |
#4
|
|||
|
|||
This table design has me stumped
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 "Allen Browne" wrote: 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TLuebke" wrote in message ... 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? |
#5
|
|||
|
|||
This table design has me stumped
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" wrote: 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TLuebke" wrote in message ... 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? |
#6
|
|||
|
|||
This table design has me stumped
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TLuebke" wrote in message ... 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" wrote: 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TLuebke" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|