Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Left Join Problems

    Hi,

    I've been working on this for a day now with no progress. I'm expereinced with SQL, but I have no experience with the syntax of Outer Joins in Access.

    Here Goes.

    I'm working with 4 Tables:

    Job | Delivery | Job_Operation| Work_Center

    Job And Delivery are connected with an INNER JOIN on Job.Job = Delivery.Job

    Job And Job_Operation are connected with a Left Join on Job.Job = Job_Operation.Job

    Job_Operation and Work_Center are connected with an Inner Join on Job_Operation.Work_Center = Work_Center.Work_Center

    I can accomplish any two of these, but when I attempt to include them all together I get one of the following errors:

    Invalid Syntax in FROM
    Join Operation Not Supported

    I didn't post the actual statement since it's obviously incorrect, but if you want me to I'd be more then happy to.

    Any help or guidance would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access wants parentheses around pairs of joined tables

    if that's not causing the error, then it could be the mix of inner/outer/inner (not sure how do adequately describe this issue)

    once you go outer, stay outer -- it will not matter, because an outer join will find exactly the same rows as an inner join, if, in fact, there are no unmatched rows

    so


    Code:
      from ((Job 
    inner
      join Delivery 
        on Job.Job = Delivery.Job)
    left outer
      join Job_Operation 
        on Job.Job = Job_Operation.Job)
    left outer
      join Work_Center 
        on Job_Operation.Work_Center = Work_Center.Work_Center
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Nov 2002
    Posts
    2

    Thumbs up THANK YOU!!!!!!!!!!!!!!!!!!

    This worked perfectly. Thanks for the help!!!!!!!!!



    Originally posted by r937
    access wants parentheses around pairs of joined tables

    if that's not causing the error, then it could be the mix of inner/outer/inner (not sure how do adequately describe this issue)

    once you go outer, stay outer -- it will not matter, because an outer join will find exactly the same rows as an inner join, if, in fact, there are no unmatched rows

    so


    Code:
      from ((Job 
    inner
      join Delivery 
        on Job.Job = Delivery.Job)
    left outer
      join Job_Operation 
        on Job.Job = Job_Operation.Job)
    left outer
      join Work_Center 
        on Job_Operation.Work_Center = Work_Center.Work_Center
    rudy
    http://rudy.ca/

Posting Permissions

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