SQL query with Many-To-Many relationship???

[boyratchet via AccessMonster.com]

I am trying to write an SQL statement that is giving me some trouble, and I
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?

 

[Michel Walsh]

SELECT *
FROM (Students INNER JOIN [Students And Classes]
ON [Students And Classes].StudentID = Students.StudentID)
INNER JOIN Classes
ON [Students And Classes].ClassID = Classes.ClassID


and you can add a WHERE clause to get only one studentID, or only one
classID. Basically, you can see the table [Students And Classes] as your
main table and the two other tables as small satellites around it, but
linked (lookup) through the specified ON clauses.



Hoping it may help,
Vanderghast, Access MVP

I am trying to write an SQL statement that is giving me some trouble, and I
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?

 

[boyratchet via AccessMonster.com]

Thanks. This certainly helps.