Create a Not In query, but show only certain records

[celinesuzzarini]

Hi all:

I have a search form, and the user is able to search for students who
haven't taken a class for a certain major.
Students is the main table, then I have a Students_Classes Table, and
another Students_majors table.
The results I am looking for are students who are in that selected
major, and students who haven't taken that class.
The problem I have, is that if a student has already taken that class,
but has taken other classes, that student is going to show up in my
list.
My query looks like the following:

SELECT DISTINCTROW Students.LastName
FROM (Students INNER JOIN [Students And Classes] ON Students.StudentID
= [Students And Classes].StudentID) INNER JOIN [Students And Majors] ON
Students.StudentID = [Students And Majors].StudentID
WHERE [Students And Majors].MajorID In (2) AND [Students And
Classes].ClassID Not In (18);

So if a student is in major 2, took class 18, but also class 21, that
student is going to show in the list... and only want students who
didn't take class 18.

I hope this is not too unclear!

Thanks,
Celine

 

[kingston via AccessMonster.com]

Think of it this way:

A query returns all majors that have the class requirement 18.
A second query returns all students with those majors where the student is
not in the subquery which returns students that have taken class 18.

Hi all:

I have a search form, and the user is able to search for students who
haven't taken a class for a certain major.
Students is the main table, then I have a Students_Classes Table, and
another Students_majors table.
The results I am looking for are students who are in that selected
major, and students who haven't taken that class.
The problem I have, is that if a student has already taken that class,
but has taken other classes, that student is going to show up in my
list.
My query looks like the following:

SELECT DISTINCTROW Students.LastName
FROM (Students INNER JOIN [Students And Classes] ON Students.StudentID
= [Students And Classes].StudentID) INNER JOIN [Students And Majors] ON
Students.StudentID = [Students And Majors].StudentID
WHERE [Students And Majors].MajorID In (2) AND [Students And
Classes].ClassID Not In (18);

So if a student is in major 2, took class 18, but also class 21, that
student is going to show in the list... and only want students who
didn't take class 18.

I hope this is not too unclear!

Thanks,
Celine

 

[celinesuzzarini]

Thanks for the reply. That works well, but do you know if there is any
way I can do it in one query only?

Thank you so much,
Celine

Think of it this way:

A query returns all majors that have the class requirement 18.
A second query returns all students with those majors where the student is
not in the subquery which returns students that have taken class 18.

Hi all:

I have a search form, and the user is able to search for students who
haven't taken a class for a certain major.
Students is the main table, then I have a Students_Classes Table, and
another Students_majors table.
The results I am looking for are students who are in that selected
major, and students who haven't taken that class.
The problem I have, is that if a student has already taken that class,
but has taken other classes, that student is going to show up in my
list.
My query looks like the following:

SELECT DISTINCTROW Students.LastName
FROM (Students INNER JOIN [Students And Classes] ON Students.StudentID
= [Students And Classes].StudentID) INNER JOIN [Students And Majors] ON
Students.StudentID = [Students And Majors].StudentID
WHERE [Students And Majors].MajorID In (2) AND [Students And
Classes].ClassID Not In (18);

So if a student is in major 2, took class 18, but also class 21, that
student is going to show in the list... and only want students who
didn't take class 18.

I hope this is not too unclear!

Thanks,
Celine

 

[kingston via AccessMonster.com]

Try to apply the first query as a subquery to the second one using IN for the
field [Major]. The query would then be based on two subqueries.

Thanks for the reply. That works well, but do you know if there is any
way I can do it in one query only?

Thank you so much,
Celine

Think of it this way:

[quoted text clipped - 30 lines]