Many to Many relationships

[Jane]

Hi

I have 3 tables

TBStudents
StudentID
Student1stName
Student2ndName
Etc

TBClasses
ClassID
ClassLocation
ClassTime
Etc

TBBook (joining table)
StudentID
ClassID
Etc

I am trying to find a way to have a list box on a form showing the students
who are NOT booked into a class.

Say I have a form showing details of Class number 123 and have a pop up form
linked to this. The popup form has a listbox based on a query so the list
precludes anyone in Class 123 ( <>123 )

This works fine unless (as often happens) a student is taking more than one
class.

So if
Student ABC is taking Class 123 (which will preclude them from the list on
the popup) but if they are also taking class 456 they will be included in the
list.

Has anyone any ideas on how to solve this.

Basically if someone is already booked into a class they will not show up in
a list – even if they have booked other classes.

Thank you for your help if possible.

Jane Hollin

 

[Ken Snell [MVP]]

Build a query that returns a list of students who are registered in the
class identified in your form:

SELECT TBStudents.StudentID
FROM TBStudents INNER JOIN TBBook
WHERE TBBook.ClassID=
Forms!NameOfClassForm!ControlWithClassIDValue

Save this query -- give it a name such as qryStudentsInClass.


Then base your list box Row Source query on this query:


SELECT TBStudents.StudentID, TBStudents.Student1stName,
TBStudents.Student2ndName
FROM TBStudents LEFT JOIN qryStudentsInClass
ON TBStudents.StudentID = TBBook.StudentID
WHERE qryStudentsInClass.StudentID IS NULL;

 

[Jane]

Thank you Mr Snell for answering

I would like if it's possible to have a box with students NOT in the class
so that I have add them to the class OnClick.

But say Student123 in in the class I can get the list box that omits
everyone in the class but if the same student is in an other class they will
show up in the list.

I think the reason is that I have in the query criteria <>123 (thats an
example if the form in on class 123)
But if the student is also in class 456 they will show up in the list as
this class is not precluded by the <>.

Do I need some other type of query

Sorry but I am not an expert with access

Thank you very much for your time

Jane Hollin

 

[Ken Snell [MVP]]

Typo in the second query:

SELECT TBStudents.StudentID, TBStudents.Student1stName,
TBStudents.Student2ndName
FROM TBStudents LEFT JOIN qryStudentsInClass
ON TBStudents.StudentID = qryStudentsInClass.StudentID
WHERE qryStudentsInClass.StudentID IS NULL;


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/