Problem Limiting Records Because of Relationship Flaw

From: Richard Hollenbeck <richard.hollenbeck_at_verizon.net>
Date: Mon, 19 Jul 2004 18:47:32 GMT
Message-ID: <8HUKc.8156$Iz3.6152_at_nwrddc01.gnilink.net>

This post is a sequel to the thread,"Re: Hopelessly circular relationships."

The relationships are not circular so I have a new question. First the scenario:

I will try to make the description a little clearer this time.

I'm building a gradebook program in MS-Access.

TABLE: Courses (key CourseID) with other fields
TABLE: Students (key StudentID) with other fields
TABLE: StudentsInCourses (composite key CourseID and StudentID) two fields

total

Students.StudentID 1:M StudentsInCourses.StudentID Courses.CourseID 1:M StudentsInCourses.CourseID

The activities in the course are categorized into groups:

TABLE: Groups (key GroupID) with other fields TABLE: Activities (key ActivitiesID) with other fields

Courses.CourseID 1:M Groups.CoursesID
Groups.ActivityID 1:M Activities.ActivityID

TABLE: StudentScores (composite key ActivityID and StudentID) with one other field -- Score

Activities.ActivityID 1:M StudentScores.ActivityID Students.StudentID 1:M StudentScores.StudentID

The idea is that each score is found in the intersection between an activity and a student.
Each combination of student and activity can only have one score; though there will be many duplicate entries of each student and each activity, there can be no duplicates of any one combination of Student and Activity.

Okay. Now I want to have a form that allows me to enter grades on a displayed list of all the students in that course LIMITS the list of students to those that are enrolled in that course, not another course. So I built a form that displays an activity with a subform based on the query:

SELECT studentScores.activityID, studentScores.studentID, studentScores.score, students.fName, students.lName FROM students INNER JOIN (activities INNER JOIN studentScores ON activities.activityID = studentScores.activityID) ON students.studentID = studentScores.studentID
ORDER BY studentScores.activityID, studentScores.studentID;

This works in that it limits the activity to the activity on the main form. However, if there is not yet any score entered, it won't display any records from the StudentScores table until I insert a StudentID. to complete the composite key. And what is also bad is that it doesn't limit the list to students in that particular course because the query is based on the Students table, not the StudentsInClasses table. If I attempt to relate the StudentScores table to the StudentsInClasses table I get an abiguous relationship because it would become a Many to Many relationship.

Is this description clearer that my previous post?

I wonder what I can do to display all the students (that are only in that particular course) as a list on the left side of the form and enter the grades corresponding to that students on the right side of the form. I know no way to eliminate students that are in other courses (i.e. not in this course.)

Thank you.
Rich Hollenbeck Received on Mon Jul 19 2004 - 20:47:32 CEST