Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: Multiple Joins to one table

    First time poster, but have been browsing gaining MS Access knowledge - the software/manipulation structure is fairly new to my skillset

    I have one table joined to another multiple times - 3 to be exact.

    2 Tables

    Table1
    IDNum1 | IDNum2 | IDNum 3

    Table2
    IDNum | Description

    In this Table1.IDNum1, Table1.IDNum2, and Table1.IDNum3 are all joined to Table2.IDNum


    In my select query I need to have the results come out similar to...

    IDNum1 | Description of IDNum1 | IDNum2 | Description of IDNum2 | IDNum3 | Description of IDNum3


    The problem I get is by just dragging the "Desciption" into design view only brings a result if all 3 'IDNum's match. I get the IdNum for all, but description is blank always

    I am uncertain how this can be done. Help!

    Edit: Access 2003, sorry
    Last edited by Kope; 06-03-08 at 18:49.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i dunno about draggin n' droppin, but i know this will work...
    Code:
    SELECT t1.IDNum1
         , d1.Description AS Description1
         , t1.IDNum2
         , d2.Description AS Description2
         , t1.IDNum3
         , d3.Description AS Description3
      FROM (((
           Table1 AS t1
    LEFT OUTER
      JOIN Table2 AS d1
        ON d1.IDNUM = t1.IDNum1
           ) 
    LEFT OUTER
      JOIN Table2 AS d2
        ON d2.IDNUM = t1.IDNum2
           )
    LEFT OUTER
      JOIN Table2 AS d3
        ON d3.IDNUM = t1.IDNum3
    paste that into an empty SQL View and see what it does when you go back to Design View...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wonder if the OP will notice the missing bracket
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    3
    Yes I did and that was right on. Each join independent and not using AND statements is where I was flawed. Thank you very much
    Last edited by Kope; 06-03-08 at 19:50.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I wonder if the OP will notice the missing bracket
    actually it was a superfluous opening bracket

    ta george
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To-MAY-to, to-MAH-to
    George
    Home | Blog

Posting Permissions

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