Forms, Sub Forms and Sub Sub Forms Advice

[Mattyn]

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,




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 and I want to add 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

[ranman256]

i use:
each course has many classes
each Class has a list of students.
the class then has assignments (tWork) ,tests, etc.
the student is graded on the assign and graded (tStudentWork)


tCourses
-----------
CourseID (auto)
subject
CourseType



tStudents
--------------
StudentID (autonumber)
FirstN
LastN
Addr
City
St
Zip
phoneH


tClasses (classes offered, Sci101, Tri202)
----------
classID (auto)
courseID
semester
Subject
Room#
ClassTime



tClassRoster (students in 1 classroom)
--------------
RosterID (auto)
ClassID (long)
StudentID (long)
FinalGrade



tWork (all tests,quizes given to class)
----------
WorkID (auto)
RosterID
WorkType (test,lab,quiz)
GradePts



tStudentWork (grade student got on test)
-------------
StudentWorkID
StudentID (long)
WorkID
Grade



tAttend (attendance per class)
---------
AttendID
StudentID
ClassDate
Present (T/F)