Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Mar 2006
    Posts
    13

    Unanswered: Student Prerequisite Query

    Hi there,

    I have a database with contact information for students. I'm using Access 2003. I have one table with student info (name, phone etc) and one table with classes and the locations of those classes(Class 1, Class 2,). These tables are linked.

    Students who want to take Class 2 have to take Class 1 first. I want to have a report (based on a query) of contact info for students who have taken Class 1, but not Class 2.

    How do I write the query for this? I have tried to do it like this:

    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID
    WHERE (((Courses.ClassLocation)="Class 1" And (Courses.ClassLocation)<>"Class 2"));

    but that of course doesn't work as I get a blank result. Can anyone help? Oh and despite the fact that it's about students and classes, this isn't homework cause yes I did read the FAQ

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    I can't see why your query not returning any records unless you do not have any records which meet the criteria.

    Aside from that, your logic is wrong imho. I try to explain by example:

    ContactID___ContactName
    001_________John
    002_________Bill
    003_________Alan

    ClassLocation__ContactID
    Class 1__________001
    Class 2__________001
    Class 1__________002
    Class 2__________003

    Using your query without the criteria (just the join) you will get:

    John____Class 1
    John____Class 2
    Bill______Class 1
    Alan____Class 2

    You can see now that applying the cirteria will give:
    John____Class 1
    Bill______Class 1

    This is wrong. I think you only want to see Bill (002) as he's the only one who has done class 1 but not class 2.

    You need to use a subquery to separately identify instances where Class 2 has been attended then use that as the exclusion criterion on your main join query. Try this:

    Code:
    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID
    WHERE Contacts.ContactID NOT IN 
          (SELECT Courses.ContactID
          FROM Courses
          WHERE Courses.ClassLocation="Class 2")
    Before you do this you might want to figure out why your first attempt did not return any records because if that didn't return records, this one certainly won't.

    hth
    Chris

  3. #3
    Join Date
    Mar 2006
    Posts
    13
    Ya, that one doesn't either....still looking at trying to figure out why the first one doesn't work...I'm missing something but I'm not sure what...

  4. #4
    Join Date
    Jun 2006
    Posts
    13
    robynz, should your WHERE clause be:

    WHERE (((Courses.ClassName)="Class 1" And (Courses.ClassName)<>"Class 2"));

    instead of:

    WHERE (((Courses.ClassLocation)="Class 1" And (Courses.ClassLocation)<>"Class 2"));

    (i.e., should you be matching up "ClassName" instead of "ClassLocation"?
    thx,
    mlt

  5. #5
    Join Date
    Mar 2006
    Posts
    13
    Quote Originally Posted by tuozzo
    robynz, should your WHERE clause be:

    WHERE (((Courses.ClassName)="Class 1" And (Courses.ClassName)<>"Class 2"));

    instead of:

    WHERE (((Courses.ClassLocation)="Class 1" And (Courses.ClassLocation)<>"Class 2"));

    (i.e., should you be matching up "ClassName" instead of "ClassLocation"?
    Doh! Ya sorry!! I tried this:

    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID
    WHERE (((Courses.ClassName)="Class 1" And (Courses.ClassName)<>"Class 2"));


    And that gets me all the records of students who have taken Class 1, but there are some on there who I know have taken Class 2 and they still show up in the result set.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Ok, so no apply tuozzo's excellent observation to the point I made earlier. My original SQL wasn't quite right though. Try this...

    Code:
    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID
    WHERE Courses.ClassName = "Class 1" and Contacts.ContactID NOT IN 
          (SELECT Courses.ContactID
          FROM Courses
          WHERE Courses.ClassName="Class 2")
    Chris

  7. #7
    Join Date
    Mar 2006
    Posts
    13
    Nope, that too returns an empty result set.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Chris's logic looks fine. However NOT IN typically runs pretty poorly. Just to play devil's advocate.... compare the performance of Chris's query and these (they should return the same results).
    Code:
    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID LEFT OUTER JOIN (SELECT ContactID
          FROM Courses
          WHERE ClassName="Class 2") AS EXCLSTUDS ON Contacts.ContactID = EXCLSTUDS.ContactID
    WHERE Courses.ClassName = "Class 1" and EXCLSTUDS.ContactID IS NULL
    Code:
    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation
    FROM Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID
    WHERE Courses.ClassName = "Class 1" and NOT EXISTS  
          (SELECT 1
          FROM Courses
          WHERE Courses.ClassName="Class 2"
          AND Contacts.ContactID = Courses.ContactID)
    You can never have too many options....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by robynz
    Nope, that too returns an empty result set.
    Oops - slow fingers...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've read and reread Chris's code - it looks fine to my (tipsy) eyes. Are you certain there are students that meet the criteria? Did you copy and paste?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2006
    Posts
    13
    Quote Originally Posted by pootle flump
    You can never have too many options....
    Unfortunately, that option throws a syntax error - missing operator

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by robynz
    Unfortunately, that option throws a syntax error - missing operator
    Which one?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - forgot about JET's little peculiarities:
    Code:
    SELECT Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, Courses.ClassName, Courses.ClassLocation FROM (Contacts INNER JOIN Courses ON Contacts.ContactID = Courses.ContactID) LEFT OUTER JOIN (SELECT ContactID FROM Courses WHERE ClassName="Class 2") AS EXCLSTUDS ON Contacts.ContactID = EXCLSTUDS.ContactID WHERE Courses.ClassName = "Class 1" and EXCLSTUDS.ContactID IS NULL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2006
    Posts
    13
    Quote Originally Posted by pootle flump
    I've read and reread Chris's code - it looks fine to my (tipsy) eyes. Are you certain there are students that meet the criteria? Did you copy and paste?
    Yes, I'm positive...there is at least one record there which matches.

    I'm now wondering about the Classes table though - the structure is as follows:

    ClassID
    ContactID
    ClassDate
    ClassName
    ClassLocation

    and then the Contacts table has just the Contact info in it. Is that why I'm not getting a set of results?

    And the one that threw a syntax error didn't do it after I closed and reopened the query. It then returned an empty set as well, as expected.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by robynz
    And the one that threw a syntax error didn't do it after I closed and reopened the query. It then returned an empty set as well, as expected.
    Yeah - I'll bet Access added the brackets I added in red (plus a few other that aren't totally necessary).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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