October 9th, 2013, 07:13 AM
-
Query with filter
I need help from you guys! Many thanks in advance! 
I've created a database of the students who 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?*"))
October 9th, 2013, 09:53 AM
-
just qadd one more condition in where clause "and (select count(*) from Courses where CustID=Customers.CustID and Courses.CourseName like 'intro?*') = 1"
so, your SQL should be like below
Code:
SELECT Customers.FirstName, Customers.Surname, Customers.Email, Courses.CourseName, Courses.CourseDate
FROM Customers INNER JOIN Courses ON Customers.CustID = Courses.CustID
where Courses.CourseName like 'intro?*'
and (select count(*) from Courses where CustID=Customers.CustID and Courses.CourseName like 'intro?*') = 1
GK
__________________________________________________ _____
if you found this post is useful click Give Rep button (bottom side on this reply ) and agree