CODEWITHSUNDEEP
sqlms-accessjoinfilterms-access-2007
user2858791
user2858791

Reputation: 11

Query with 2 filters in Access

I'm doing work experience where I've created a database of past students in Microsoft Access 2007.

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 Two tables: Customers and Courses with an 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 111 students who did Introduction course. But some of them did Intermediate course as well or both Intermediate course and Advanced course as well.

I need to filter students who have done Introduction course but nothing else;

And who have done both Introduction course and Intermediate course but nothing else;

I'm struggling to separate those students

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?*"))
ORDER BY Customers.FirstName;

The current result is 111 students. But more than half of them did other courses as well(if you count manually).

I need to filter students who have done Introduction course only but nothing else (the list of students - their details, who did not do any other course, just Introduction);

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" .

Courses Table

CourseID CourseName CourseDate CustID Notes

There is a list of other courses in the "Courses Table" Advanced Airbrush Bridal Hair Intermediate Introduction Refreshner

Customer Table

CustID FirstName Surname Email Phone Address1 Address2 Address3 DateOfBirth Extra info

That's my problem. I still can not figure out how to do that students with other courses shall not be part of the result???

This is the code I came up with to find out Who Did more than Introduction course:

SELECT Customers.FirstName, Customers.Surname, Customers.Email,      Count(Courses.CourseName) AS CountOfCourseName, Courses.CustID
FROM Customers INNER JOIN Courses ON Customers.CustID = Courses.CustID
GROUP BY Customers.FirstName, Customers.Surname, Customers.Email, Courses.CustID
HAVING (((Count(Courses.CourseName))>1));

Now I need to figure out how to hide them but instead to display who did only Introduction course and nothing else (?).

Upvotes: 1

Views: 257

Answers (1)

Jonathan Root
Jonathan Root

Reputation: 554

Generally I suggest you read about sub-queries and combining queries. In your attempts you miss the "but not like" dependency between customers and courses. I tried the following in PostgreSQL, so I don't know if this adapted version works in Access. It also might not be the most efficient solution.

SELECT FirstName, Surname, Email, CourseName, CourseDate, Customers.CustID
    FROM Customers INNER JOIN Courses ON Customers.CustID = Courses.CustID
    WHERE CourseName LIKE "Intro*" AND Courses.CustID NOT IN
        (SELECT CustID
            FROM Courses
            WHERE CourseName IN ("Advanced", "Airbrush", "Bridal", "Hair", "Intermediate", "Refreshner"))
ORDER BY FirstName;

For your second query you have switch "Introduction" with "Intermediate". That will give you all customers who only attended the Intermediate course. Then you have to use UNION, to combine this query with your first one (SELECT [A] UNION SELECT [B]). Of course the ORDER BY FirstName in the middle has to be removed, as well as the semicolon in the middle. This will get you all customers who attended the Introduction and Intermediate course, but nothing else. Again, this might not be the most efficient solution.

Upvotes: 1

Related Questions