query on multiple tables

[harleyken]

To all;
My form has [combo2] from Classes_Available and stores the ID to be used
later. when the combo2 after update opens a sub form which shows all of the
classes that are scheduled from Scheduled_Classes.

What I am trying to do is build a query that checks to see if a person is
already scheduled for a class type so they don't get scheduled for the same
class type twice...

Table
Students
[ID]
{and other info}
Classes_Available
[ID]
[Class_Type]
{and other info}
Scheduled_Classes
[ID]
[ClassID]
[Start_Date]
{and other info/same class type can be scheduled several times}
Scheduled_Student
[StudentID]
[ScheduledClassesID]

 

[harleyken]

I should add -
the form has students ID, so I want the query to get the type of class from
combo2 and compare that against the classes the student is already scheduled
for...

 

[John Spencer]

One Method:

SELECT S.*
FROM Students as S
WHERE Not Exists (
SELECT *
FROM (Scheduled_Student as SS INNER JOIN Scheduled_Classes as SC
ON SS.ScheduledClassesID = SC.ID)
INNER JOIN Classes_Available as C
ON SC.ClassID = ClassID
WHERE SC.StudentID = S.ID AND C.Class_Type = "Emegency")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[harleyken]

Thanks for the reply!

I got "syntax error on query expression 'not exist ( select....."

I am using 2007 if that makes a difference..

One Method:

SELECT S.*
FROM Students as S
WHERE Not Exists (
SELECT *
FROM (Scheduled_Student as SS INNER JOIN Scheduled_Classes as SC
ON SS.ScheduledClassesID = SC.ID)
INNER JOIN Classes_Available as C
ON SC.ClassID = ClassID
WHERE SC.StudentID = S.ID AND C.Class_Type = "Emegency")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

To all;
My form has [combo2] from Classes_Available and stores the ID to be used
later. when the combo2 after update opens a sub form which shows all of
the
classes that are scheduled from Scheduled_Classes.

What I am trying to do is build a query that checks to see if a person is
already scheduled for a class type so they don't get scheduled for the
same
class type twice...

Table
Students
[ID]
{and other info}
Classes_Available
[ID]
[Class_Type]
{and other info}
Scheduled_Classes
[ID]
[ClassID]
[Start_Date]
{and other info/same class type can be scheduled several times}
Scheduled_Student
[StudentID]
[ScheduledClassesID]

 

[John Spencer]

I missed referring to the Classes_Available tabe when I specified the join
in the subquery

SELECT S.*
FROM Students as S
WHERE Not Exists (
SELECT *
FROM (Scheduled_Student as SS INNER JOIN Scheduled_Classes as SC
ON SS.ScheduledClassesID = SC.ID)
INNER JOIN Classes_Available as C
ON SC.ClassID = C.ClassID
WHERE SC.StudentID = S.ID AND C.Class_Type = "Emegency")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the reply!

I got "syntax error on query expression 'not exist ( select....."

I am using 2007 if that makes a difference..

One Method:

SELECT S.*
FROM Students as S
WHERE Not Exists (
SELECT *
FROM (Scheduled_Student as SS INNER JOIN Scheduled_Classes as SC
ON SS.ScheduledClassesID = SC.ID)
INNER JOIN Classes_Available as C
ON SC.ClassID = ClassID
WHERE SC.StudentID = S.ID AND C.Class_Type = "Emegency")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

To all;
My form has [combo2] from Classes_Available and stores the ID to be
used
later. when the combo2 after update opens a sub form which shows all
of
the
classes that are scheduled from Scheduled_Classes.

What I am trying to do is build a query that checks to see if a person
is
already scheduled for a class type so they don't get scheduled for the
same
class type twice...

Table
Students
[ID]
{and other info}
Classes_Available
[ID]
[Class_Type]
{and other info}
Scheduled_Classes
[ID]
[ClassID]
[Start_Date]
{and other info/same class type can be scheduled several times}
Scheduled_Student
[StudentID]
[ScheduledClassesID]