Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclude second course from query MS Access 2007

    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?

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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)

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Maybe post the SQL for each of the queries, so that I can have a better view of the problem.

  5. #5
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  7. #7
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •