Exclude second course from query MS Access 2007

Topic

New Reply

I have a query set up that lists courses taken by individuals. Simple stuff. The client now wants a report that excludes the introductory course (Course I) from the list if a student has taken the advanced course (Course II). I have created a query listing all students taking Course I and another query listing all students taking Course II to come up with a list of IDs of those who took BOTH. How can I use that information to get Access to show only Course II in the query? :huh:

Reply | Quote

Replies

Get the query for course II and add a WHERE clause of the type:

WHERE STUDENT_ID NOT IN (SELECT STUDENT_ID FOR STUDENTS WHO TOOK COURSE I)

Reply | Quote

Yes, but I can’t exclude the student because he/she may also have taken Course A and Course B. I just need to exclude Course I from the listing.

Reply | Quote

Maybe post the SQL for each of the queries, so that I can have a better view of the problem.

Reply | Quote

The initial query is simple:
SELECT tblStudents.StudentID, tblCourses.CourseName
FROM (tblStudents INNER JOIN tblCoursesTaken ON tblStudents.StudentID = tblCoursesTaken.StudentID) INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID;

To determine which students took Course I, it’s simply a matter of using that as a criteria:
SELECT tblCoursesTaken.StudentID, tblCourses.CourseName
FROM tblCourses INNER JOIN tblCoursesTaken ON tblCourses.CourseID = tblCoursesTaken.CourseID
WHERE (((tblCourses.CourseName)=”Course I”));

or, more simply, if I look only for the courseID (assuming I know it),

SELECT tblCoursesTaken.StudentID, tblCoursesTaken.CourseID
FROM tblCoursesTaken
WHERE (((tblCoursesTaken.CourseID)=10));

Same thing to look for Course II, of course. And then I can easily determine which students took both. The issue is that I need to EXCLUDE Course 2 (CourseID 11, in my case) only for those students who took Course I. However, if that student also took CourseID 1 to 9 or 12 onwards, I still need to include that student in the query. So I can’t exclude the student; I can only exclude the course. I don’t want to delete history, so the fact that they took the “introductory” course remains in their student record. The client just doesn’t want to see it in the report.

Reply | Quote

Use a UNION query.

1st query gets students who take the 2nd course, 2nd one just the students who took the first course but not the 2nd, and the UNION gets all the records together

You query one would be something like this:

Code:

SELECT tblStudents.StudentID, tblCourses.CourseName
FROM ((tblStudents INNER JOIN tblCoursesTaken ON tblStudents.StudentID = tblCoursesTaken.StudentID) 
          INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID)
WHERE (tblCoursesTaken.CourseID=11)

UNION

SELECT tblStudents.StudentID, tblCourses.CourseName
FROM (tblStudents INNER JOIN tblCoursesTaken ON tblStudents.StudentID = tblCoursesTaken.StudentID) 
          INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID
WHERE (tblCoursesTaken.CourseID=10) AND tblStudents.StudentID NOT IN 
           (SELECT tblCoursesTaken.StudentID FROM tblCoursesTaken WHERE tblCoursesTaken.CourseID=11)

You can change the order of the queries, it is irrelevant.

Reply | Quote

Thanks for suggesting a union query. I don’t usually use them, so I never considered it. When I tried your example, I was still missing those individuals who took CourseA or CourseB, so I added one additional UNION query to catch everyone else:
UNION

SELECT tblCoursesTaken.StudentID, tblCourses.CourseName
FROM tblCoursesTaken INNER JOIN tblCourses ON tblCoursesTaken.CourseID = tblCourses.CourseID
WHERE ((Not (tblCoursesTaken.CourseID)=10 And Not (tblCoursesTaken.CourseID)=11));

That seems to have done the trick. Thanks!

Reply | Quote