B

#### boyratchet via AccessMonster.com

was hoping that somebody here could lend me a hand.

CONTEXT

I am working in Access 2007. The tables concerned are the Students, Classes

and StudentsAndClasses.

The relationship of Students to Classes is many-to-many, i.e. each student

can attend several classes and each class can have several students.

StudentsAndClasses table is used to help define this relationship and works

something like a class roster.

The Students table has StudentID as its primary key.

The Classes table has ClassID as its primary key.

The StudentsAndClasses table has StudentID and ClassID as foreign keys.

The relationship of Students to StudentsAndClasses is 1 to many.

The relationship of Classes to StudentsAndClasses is 1 to many.

There is a Students form which shows student details and a corresponding

subform which shows all the classes the student is in.

There is a Classes form which shows class details and a corresponding subform

which shows all the students in the class.

PROBLEM

The user is looking at a student on the Students form. In the Classes Subform,

the classes that the student is in are listed. There is a "Go To Class"

button that should pull up the Classes form and the details of the

corresponding classes (those appearing on the Students form when the "Go To

Button" was clicked), which includes a list of the students taking each class

(on display in the Students Subform). In other words, for any given student,

that a user might be looking at I want a record set that contains all the

classes that the student attends as well as the corresponding list of

students in each of those classes.

I've been trying to do it by means of a query.

This first query will get all of the classes that a student is in:

SELECT *

FROM Classes INNER JOIN [Students And Classes]

ON [Students And Classes].ClassID = Classes.ClassID

WHERE [Students And Classes].StudentID=SomeStudentID

This next query will get all of the students that a particular class has:

SELECT *

FROM Students INNER JOIN [Students And Classes]

ON [Students And Classes].StudentID = Students.StudentID

WHERE [Students And Classes].ClassID=SomeClassID

I can't figure out how to combine the two and then bind the record set to the

Classes form.

The closest I have gotten is to have the Classes form appear with the correct

classes, but I can't figure out how to have the corresponding details (the

students in the class) to appear in the sub form automatically. If not an SQL

statement, is there a way to get the sub form to update based on the details

passed to the main form?