Good Afternoon All
I am building a Db for a school that runs many courses. Each course can have many classes. Students can be enrolled on many courses, and maybe on many classes should they fail first time round. A student may repeat a course on another class should he fail the exam, for instance.
This database under construction utilises the following tables.
tblRank (a list of the ranks of each student - forms the basis of a cbo for the tblStudent.
tblStudent (a standard list of all Student data).
tblCourses (all the data per course we deliver - each course has a code number and a title.)
tblCourseDelivery (a table of all classes of each course we deliver. Some courses only have one class delivered each year, some have 12 classes per year annotated 01/16, 02/16, 03/16 etc).
tblStudentEnrolment (which student is on which course and in which class).
Current relationships are as follows - the pic is cut off on the right, but all tables are visible.
I want to be able to load students into course classes by either the course or student form, therefore from those tables, I have created the following:
frmtblStudentMain - This is a direct form adaption of tblStudent - created using the wizard as a columnar form
subfrmtblStudentEnrolment - a sub form added to frmtblStudentMain and linked by StudentID, This shows and can update all the course classes a student in on (enrolled). It is tabular (continuous) as I want to see all the classes a student is enrolled in.
frmCoursesMain - a direct form adaption of tblCourses - created using the wizard as a columnar form. In this form underneath the columnar stuff, I created a tab controlled sub form, with two tabs.
tab 1 - subfrmCourseDelivery - linked by CourseID, it is used to create each individual class for each course. Is in tabular format to show each class on the course page of the frmCoursesMain. I will use this to create classes within each course.
tab 2 - subfrmCourseloader - a columnar version of subfrmCourseDelivery as I want a sub-subform to be able to load students onto each class. It is adding this sub-subform that I am having snags with.
I thought I had this sorted but it appears I have duplication of data. Should StudentA repeat course A by attending and failing Class 01/16 and then be enrolled in Class 02/16, and I complete this on the frmStudentMain and subfrmtblStudentEnrolment form his data is duplicated in subfrmCourseLoader (i.e. there appears to be two records). I have, obviously, class 01/16 is only unique when linked with the courseID.
I am using a subform within a subform within a form, and this is causing an issue - or is it, as I think probably, I am just simply not linking it correctly.
Can anyone offer a suggestion on how to make these forms do what I want? I am happy to start again to a certain extent, as this is a good learning experience for me, but I do want and need two ways of loading and viewing students that are on onto courses.
Thoughts, suggestions, advice much appreciated. I really hope all this makes sense!!
Many, many thanks in advance.
Matt
I am building a Db for a school that runs many courses. Each course can have many classes. Students can be enrolled on many courses, and maybe on many classes should they fail first time round. A student may repeat a course on another class should he fail the exam, for instance.
This database under construction utilises the following tables.
tblRank (a list of the ranks of each student - forms the basis of a cbo for the tblStudent.
tblStudent (a standard list of all Student data).
tblCourses (all the data per course we deliver - each course has a code number and a title.)
tblCourseDelivery (a table of all classes of each course we deliver. Some courses only have one class delivered each year, some have 12 classes per year annotated 01/16, 02/16, 03/16 etc).
tblStudentEnrolment (which student is on which course and in which class).
Current relationships are as follows - the pic is cut off on the right, but all tables are visible.
I want to be able to load students into course classes by either the course or student form, therefore from those tables, I have created the following:
frmtblStudentMain - This is a direct form adaption of tblStudent - created using the wizard as a columnar form
subfrmtblStudentEnrolment - a sub form added to frmtblStudentMain and linked by StudentID, This shows and can update all the course classes a student in on (enrolled). It is tabular (continuous) as I want to see all the classes a student is enrolled in.
frmCoursesMain - a direct form adaption of tblCourses - created using the wizard as a columnar form. In this form underneath the columnar stuff, I created a tab controlled sub form, with two tabs.
tab 1 - subfrmCourseDelivery - linked by CourseID, it is used to create each individual class for each course. Is in tabular format to show each class on the course page of the frmCoursesMain. I will use this to create classes within each course.
tab 2 - subfrmCourseloader - a columnar version of subfrmCourseDelivery as I want a sub-subform to be able to load students onto each class. It is adding this sub-subform that I am having snags with.
I thought I had this sorted but it appears I have duplication of data. Should StudentA repeat course A by attending and failing Class 01/16 and then be enrolled in Class 02/16, and I complete this on the frmStudentMain and subfrmtblStudentEnrolment form his data is duplicated in subfrmCourseLoader (i.e. there appears to be two records). I have, obviously, class 01/16 is only unique when linked with the courseID.
I am using a subform within a subform within a form, and this is causing an issue - or is it, as I think probably, I am just simply not linking it correctly.
Can anyone offer a suggestion on how to make these forms do what I want? I am happy to start again to a certain extent, as this is a good learning experience for me, but I do want and need two ways of loading and viewing students that are on onto courses.
Thoughts, suggestions, advice much appreciated. I really hope all this makes sense!!
Many, many thanks in advance.
Matt
Last edited:
