#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    Post 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?*"))
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    283
    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

Similar Threads

  1. Need hep in Query Filter
    By loy_ramos in forum ASP Development
    Replies: 2
    Last Post: September 14th, 2009, 03:35 AM
  2. To use a query or not to filter out records
    By AirForce Ed in forum Microsoft Access Help
    Replies: 9
    Last Post: April 29th, 2008, 03:41 PM
  3. [Query] Help with criteria to filter certain data
    By soeh in forum Microsoft Access Help
    Replies: 4
    Last Post: February 2nd, 2008, 11:24 PM
  4. A way to set query filter from code?
    By medmister in forum Microsoft Access Help
    Replies: 2
    Last Post: June 6th, 2006, 02:35 AM
  5. Filter Query
    By Niall in forum Microsoft Access Help
    Replies: 0
    Last Post: January 5th, 2005, 05:41 AM

IMN logo majestic logo threadwatch logo seochat tools logo