Query with 2 filters

[alex2013]

I need help from you guys! Many thanks in advance!




I've created a database of the students cwho did some courses using the Microsoft Access 2007.


I have Two tables: Customers and Courses wit one to many relationship: each customer/student can attend one or more courses.


So there are students who did one or more than one courses. I have problem with creating a query to filter students who have done Introduction Course but nothing else. I guess I need 2 filters.


I have 111 students who did Introduction course, more than half of them did other courses as well(if you count manually). But some of them did Intermediate course as well or Intermediate course and Advanced course as well.




I'm struggling to separate those students




I want to create a query to find out who have done Introduction course but nothing else;


Second query is to find out who have done"Introduction course and Intermediate course but nothing else"


Could anyone help me please...
-----------------------------------

At the moment my SQL statement looks like this:

SELECT Customers.FirstName, Customers.Surname, Customers.Email, Courses.CourseName, Courses.CourseDate, Customers.CustID
FROM Customers INNER JOIN Courses ON Customers.CustID = Courses.CustID
WHERE (((Courses.CourseName) Like "Intro?*"))

[NTC]

So you need to approach it differently.
Query1 = all that took Course A.
Query2= all that took Couse A and B (note that you need every combination if one can take A and C while skipping B, etc).
Query 3 is a find no match/duplicate query that for which there is a wizard. All in Q1 that are not in Q2.

Hope this helps get you on the right path.

[Dal Jeanis]

Given the assumption that your intermediate courses have a name like "Inter?*"...

Query1:
SELECT 
  CU.CustID,
  CU.FirstName, 
  CU.Surname, 
  CU.Email, 
  C1.CourseDate, 
  C1.CourseName 
FROM 
  Customers AS CU
  INNER JOIN 
  Courses AS C1
  ON CU.CustID = C1.CustID
WHERE C1.CourseName Like "Intro?*") 
AND CU.CustiD NOT IN
      (SELECT C2.CustID
      FROM Courses AS C2
      WHERE C2.CourseName Like "Inter?*") ;

Query2:
SELECT 
  CU.CustID,
  CU.FirstName, 
  CU.Surname, 
  CU.Email, 
  C1.CourseDate, 
  C1.CourseName,
  C2.CourseDate, 
  C2.CourseName
FROM 
  Customers AS CU
  INNER JOIN 
       (SELECT C1.CustID,C1.CourseDate,C1.CourseName,
       FROM Courses AS C1
       WHERE (C1.CourseName Like "Intro?*")) 
    INNER JOIN
       (SELECT C2.CustID
       FROM Courses AS C2
       WHERE (C2.CourseName Like "Inter?*")) ;
    ON C1.CustID = C2.CustID
  ON CU.CustID = CO.CustID;
WHERE CU.CustiD NOT IN
      (SELECT C3.CustID
      FROM Courses AS C3
      WHERE ((C3.CourseName NOT Like "Intro?*")
      AND  (C3.CourseName NOT Like "Inter?*"))) ;