Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    15

    Unanswered: Help with Query (conversion from SQL)

    Hi.

    I might be up the creek without a paddle, but I hope you guys can help me... I want to convert this T-SQL statement into an Acess 97 query

    select * from [table1] VP
    inner join [table2] VD on VD.Counter = VP.Counter
    left join [table3] AW on AW.Id = VP.Id and VD.Task = AW.Task

    My problem is that no matter how i structure the query or if i use subqueries in access it gives me an error. I suspect it is because i am technically left joining to 2 tables using different columns (Which access does not support).

    It is worthwhile saying that this script works in SQL.. (I exclude where clause and real table names, column names due to security issues....)

    Thanks for any help.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I have no idea if this will work as I haven't touched Access 97 in years. But you might try this and see what happens.
    Code:
    SELECT columns ...
    FROM (SELECT columns ...
          FROM table1 VP
                 INNER JOIN
               table2 VD
                 ON VD.counter = VP.counter
         ) AS NT
           LEFT OUTER JOIN
           table3 AW
             ON AW.Id = NT.Id and AW.Task = NT.Task

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Megacale
    I want to convert this T-SQL statement into an Acess 97 query
    just add a pair o' parentheses for every additional table join beyond the first
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star 
      FROM (
           [table1] VP
    INNER 
      JOIN [table2] VD 
        ON VD.Counter = VP.Counter
           )
    LEFT OUTER
      JOIN [table3] AW 
        ON AW.Id = VP.Id 
       AND AW.Task = VD.Task
    the general pattern would be
    Code:
      FROM (((((....    -- total n minus 1 parens
           table_1
    INNER
      JOIN table_2
        ON ...
           )            -- close 1st paren         
    INNER
      JOIN table_3
        ON ...
           )            -- close 2nd paren         
    ...
    INNER
      JOIN table_nminus1
        ON ...
           )            -- close n-1 paren         
    INNER
      JOIN table_n
        ON ...

    Last edited by r937; 06-26-09 at 15:30.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2009
    Posts
    15
    Ok thanks, ill get back to you guys shortly...

  5. #5
    Join Date
    Apr 2009
    Posts
    15
    It worked. Seems like my original problem wasnt because of the joins but rather a data type mismatch on "AW.Task = NT.Task". After a bit of playing around i got it to work.

    Sucks to support a 10 year old access platform.. lol Thanks for the quick responses.

Posting Permissions

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