Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered: Simple Database, Challenging Query.

    I am helping out a family member by making a very basic database that stores Staff-Members, Courses and a history of Courses completed (see relationship view below):

    Click here for an image of the database tables and their relationships

    As you can see, it's a very simple database that keeps a history of all the courses that a particular staff member has completed.

    The challenge that I currently face, arises when I try to think of a way to ask the database to "Show me all the COURSES from the COURSES table, that a particular STAFF member has NOT YET completed". Since the Course_History table only holds data that shows courses that HAVE been completed, how would I go about getting the desired NOT COMPLETED data?

    Any assistance would be much appreciated, as I'm not really sure where to start with this one!

    Thanks,
    Jarrad

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.Subject    
      FROM Courses AS c
    LEFT OUTER
      JOIN Course_History AS ch
        ON ch.Module_ID = c.Module_ID
       AND ch.Staff_ID = 937
     WHERE ch.Staff_ID IS NULL
    you might have to use parentheses around the ANDed conditions in the ON clause

    stupid Access and its parentheses...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as an alternative look use one of the query wizards to examine the syntax JET uses for joins/missing and so on
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2009
    Posts
    5
    Thanks for quick the replies r937 and healdem!

    Sorry to be a pain, but I cannot get it to work, I get the error:
    "JOIN expression not supported" from Access when I try to run the query, and it highlights the AND line of code.

    Code:
    SELECT Courses.Subject
    FROM Courses AS c
    LEFT OUTER 
    JOIN Course_History AS ch 
    ON ch.Module_ID = c.Module_ID 
    AND ch.Staff_ID = 000000
    WHERE ch.Staff_ID IS NULL
    I've tried a number of placements for the parentheses, but cannot get it to run correctly.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so do the same thing in the query designer within Access
    access uses a different syntax for its joins
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.Subject
      FROM Courses AS c
    LEFT OUTER 
      JOIN Course_History AS ch 
        ON (
           ch.Module_ID = c.Module_ID 
       AND ch.Staff_ID = 000000
           )
     WHERE ch.Staff_ID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2009
    Posts
    5
    Great, Thanks for the assistance guys!

    Interesting thing I found while playing around with this, was that Access cannot display the query in Design View. It throws up errors and complains, but quite happily runs the query from the SQL view and displays the results in the datasheet.

    One more thing that I'd like to do with this, is add columns to the results showing Staff.FIRSTNAME and Staff.LASTNAME, would this be done with a nested query, or am I on the wrong track?

    Thanks so much for your time, I'd be lost without it,
    Jarrad

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    here's a wild suggestion
    why not try it
    you'll learn an awful lot more and quicker by trying rather than posting a 'can I do this' question on forums
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2009
    Posts
    5
    All working correctly, thanks very much for your help r937!

Posting Permissions

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