Are ad hoc pseudo relationships possible?

[Richard Hollenbeck]

I need to pull data from an unrelated table. I cannot, as far as I know,
relate these two tables because they would create a many-to-many
relationship, which I will explain in a second.

Anyway, I need to insert studentID from the StudentsInCourses table into new
records in the StudentGrades table. When I open the "Add Student Grades"
form, it won't display the roster of students in the class because there
the composite key between the Students table "studentID" and Activities
table "activityID" isn't yet satisfied. Once I enter a studentID, all is
fine; because the form is filtered for "activityID," the composite key is
satisfied and a new record is created. I can then enter a score for that
student in that activity. But the Students table doesn't know which
students are in which course. Therefore, it won't prohibit me from entering
the studentID of a student who is in another course.

The StudentsInCourses table is on the many side of a 1:M relationship with
both the Students table and the Courses tables. The StudentScores table is
on the many side of the 1:M relationship with the Students table and also is
indirectly related to the Courses table (via Activities) and is on the many
side.

In other words, both StudentsInCourses and StudentScores are on the many
side of both Students and Courses. So I really can't relate StudentScores
with StudentsInCourses, I don't think so anyway. It would create a
many-to-many nightmare--wouldn't it?

So if I cannot relate the two tables, how do I limit a list of students in
the StudentGrades table to students in a particular course? Is there a
clever work-around? Is there some kind of pseudo relationship I could
create programmatically just for this one purpose with no permanent
relationship?

 

[Richard Hollenbeck]

ADDENDUM:
I need to create a temporary one-to-one relationship between
StudentsInClasses and StudentScores. I will not need to modify data in the
StudentsInClasses from here; I just need filtered data source to populate an
activity with students so I can add/edit grades.

 

[Richard Hollenbeck]

Oops! It's not actually the StudentScores table, but an "Enter Student
Scores" form based on a query which includes Activities which is on the many
side of an indirect join to the Courses table. The form Has a CourseCode,
as does the StudentsInClasses table.

 

[Tim Ferguson]

It would create a
many-to-many nightmare--wouldn't it?

Sorry: I didn't really understand all the description; but Many-to-many
relationships are not a nightmare, just standard practice.

This much I understood:


Students
========
*StudentID -----+ SiCourses SGrades
| ========= =======
+-------< *StudentID ------< *StudentID
Activities +-------< *ActivityID -----< *ActivityID
========== | *Semester
*ActivityID -----+



Which is pretty straightforward. I don't understand your UI at all! I think
that I would have a registration form that is based on Activities, with an
unbound text box that shows which students are registered on it. I would
bring up a second dialog (= form) to add new students to the list, and
catch the double-click event on the list box to add SGrades records via
another dialog. Or perhaps a second list box that shows the grades for
whichever student is selected in the first one: that is probably more
intuitive for the user.

You might get more coherent help in the FormsCoding group!

HTH


Tim F