Results 1 to 13 of 13
  1. #1
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16

    Rewriting SQL Server Query to Access query not without problems

    I've set up access to use a 'linked table' so all tables etc are the same.



    I've written this query which works just fine in SQL Server
    Code:
    SELECT Stoel.Stoelnummer, Rang, B.Stoelnummer
      FROM Uitvoering
      JOIN Stoel
        ON Stoel.Zaal = Uitvoering.Zaal
    LEFT JOIN(
        SELECT Bezetting.Stoelnummer
          FROM Uitvoering
          JOIN Bezetting 
            ON Bezetting.Voorstellingsnummer = Uitvoering.Voorstellingsnummer
           AND Bezetting.Uitvoeringsnummer = Uitvoering.Uitvoeringsnummer
            AND Bezetting.Voorstellingsnummer = 1 AND Bezetting.Uitvoeringsnummer = 1
    ) AS B
        ON B.Stoelnummer = Stoel.Stoelnummer
        WHERE Uitvoering.Voorstellingsnummer = 1 AND Uitvoering.Uitvoeringsnummer = 1
    Then I rewrote the query to access and ended up with the following:
    Code:
    SELECT dbo_Stoel.Stoelnummer, Rang, B.Stoelnummer
    FROM dbo_Uitvoering
    INNER JOIN dbo_Stoel
        ON dbo_Stoel.Zaal = dbo_Uitvoering.Zaal
    LEFT JOIN(
    SELECT dbo_Bezetting.Stoelnummer
    FROM dbo_Uitvoering
    INNER JOIN dbo_Bezetting
    ON dbo_Bezetting.Voorstellingsnummer = dbo_Uitvoering.Voorstellingsnummer
    AND dbo_Bezetting.Uitvoeringsnummer = dbo_Uitvoering.Uitvoeringsnummer
    AND dbo_Bezetting.Voorstellingsnummer = 1 AND dbo_Bezetting.Uitvoeringsnummer = 1
    ) AS B
    WHERE dbo_Uitvoering.Voorstellingsnummer = 1 AND dbo_Uitvoering.Uitvoeringsnummer = 1
    Which gives me a missing operator error (see attachment)

    What's going wrong?

  2. #2
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16
    Well I figured out what I'm doing wrong (or imo Access is doing wrong).

    It seems it's required to use parenthesis, for example this works
    Code:
    SELECT *
    FROM (dbo_Bezetting AS B
    INNER JOIN dbo_Uitvoering AS U
      ON U.Voorstellingsnummer = B.Voorstellingsnummer AND U.Uitvoeringsnummer = B.Uitvoeringsnummer)
    LEFT JOIN dbo_Stoel AS S
     ON B.Stoelnummer = S.Stoelnummer
    Remove the parenthesis and it doesn't work and throws a missing operator error.
    I don't quite get where they should be placed in the query in my first post however as it uses several joins, any help would be much appreciated!

  3. #3
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    SELECT dbo_Stoel.Stoelnummer, Rang, B.Stoelnummer
    FROM dbo_Uitvoering
    INNER JOIN dbo_Stoel
    ON dbo_Stoel.Zaal = dbo_Uitvoering.Zaal
    LEFT JOIN(
    SELECT dbo_Bezetting.Stoelnummer
    FROM dbo_Uitvoering
    INNER JOIN dbo_Bezetting
    ON dbo_Bezetting.Voorstellingsnummer = dbo_Uitvoering.Voorstellingsnummer
    AND dbo_Bezetting.Uitvoeringsnummer = dbo_Uitvoering.Uitvoeringsnummer
    AND dbo_Bezetting.Voorstellingsnummer = 1 AND dbo_Bezetting.Uitvoeringsnummer = 1
    ) AS B
    WHERE (dbo_Uitvoering.Voorstellingsnummer =) 1 AND (dbo_Uitvoering.Uitvoeringsnummer =) 1;
    Check it here..

  4. #4
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16
    Thanks for replying Khalid, no luck with that either though. I'm still getting a "Syntax error (missing operator)".

    I did solve one other error however.. the query in the left join used two ands to look up a static value which access apparently doesn't support. Had to put that in a where clause instead (SQL Server does support that syntax).

    So this what I got now (which is still throwing errors instead of working):
    Code:
    SELECT dbo_Stoel.Stoelnummer, Rang, B.Stoelnummer
    FROM dbo_Uitvoering
      INNER JOIN dbo_Stoel
      ON dbo_Stoel.Zaal = dbo_Uitvoering.Zaal
      LEFT JOIN(
        SELECT dbo_Bezetting.Stoelnummer
        FROM dbo_Uitvoering
        INNER JOIN dbo_Bezetting
        ON dbo_Bezetting.Voorstellingsnummer = dbo_Uitvoering.Voorstellingsnummer
        AND dbo_Bezetting.Uitvoeringsnummer = dbo_Uitvoering.Uitvoeringsnummer
        WHERE dbo_Bezetting.Voorstellingsnummer = 1 AND dbo_Bezetting.Uitvoeringsnummer = 1
      ) AS B
    WHERE (dbo_Uitvoering.Voorstellingsnummer) = 1 AND (dbo_Uitvoering.Uitvoeringsnummer =) 1;

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe typo, in the outer query WHERE clause the = sign is within parenthesis:
    WHERE (dbo_Uitvoering.Voorstellingsnummer) = 1 AND (dbo_Uitvoering.Uitvoeringsnummer =) 1;
    Remove parens:
    WHERE dbo_Uitvoering.Voorstellingsnummer = 1 AND dbo_Uitvoering.Uitvoeringsnummer = 1;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16
    Added those as per Khalids advice, tried with and without though. Both ways giving me the same error so I'm guessing it's finding it doesn't like before it even reaches that part.

    All these oddities in the SQL variant that Access sure are frustrating.

  7. #7
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    OK!
    Axeia, is it possible for you to manually drag your tables in the Microsoft Access QBE (query design) interface and re-link it through INNER and LEFT joins with the WHERE clause by just typing 1 in the criteria expression for both dbo_Uitvoering.Voorstellingsnummer and dbo_Uitvoering.Uitvoeringsnummer?

    The Microsoft Access will automatically create the SQL accordingly.

  8. #8
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16
    Worth a try, but I'm guessing this might be a bit problematic if not downright impossible due to some fields not actually being related (didn't write the database myself). Kinda tired of this finicky business for now, I'll give a try tomorrow update this post with the result.
    Unless someone posts before that with a corrected query ofcourse

  9. #9
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    The problem is in WHERE clause I believe...
    WHERE (((dbo_Uitvoering.Voorstellingsnummer) = 1) AND ((dbo_Uitvoering.Uitvoeringsnummer) =1));
    This should solve the problem.

  10. #10
    Axeia is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    16
    Okay nailed it!
    With a combination of dumb luck and looking at what the QBE generates I finally ended up with something that executes.
    Code:
        SELECT S.Stoelnummer, Rang, BS.Stoelnummer
         FROM (dbo_Uitvoering AS U INNER JOIN dbo_Stoel AS S
           ON (S.Zaal = U.Zaal))
    LEFT JOIN( 
      SELECT dbo_Bezetting.Stoelnummer
        FROM dbo_Uitvoering INNER JOIN dbo_Bezetting 
          ON (dbo_Uitvoering.Uitvoeringsnummer = dbo_Bezetting.Uitvoeringsnummer) 
         AND (dbo_Uitvoering.Voorstellingsnummer = dbo_Bezetting.Voorstellingsnummer)
       WHERE (((dbo_Bezetting.Voorstellingsnummer)=1) AND ((dbo_Bezetting.Voorstellingsnummer)=1))
    ) AS BS
          ON BS.Stoelnummer = S.Stoelnummer
       WHERE (U.Voorstellingsnummer = 1) AND (U.Uitvoeringsnummer = 1);
    The dumb luck part is in the placement of parenthesis in the last line.. one would expect it would need to be written as in the inner-query as it's practically the same line. Apparently not as this makes access throw a "Syntax error, Too many )", which isn't helpful when you have 15 opening and closing parenthesis.. just started trying every combination till it worked.

    Thanks guys, I'll mark it as solved but if anyone figures out how these parenthesis work please do feel free to add a post. I find them rather confusing and they aren't required in other SQL Dialects like T-SQL/MySQL/PostgreSQL/SQLite/Derby.
    Last edited by Axeia; 05-22-2011 at 06:50 AM. Reason: SQL tag acted up, no highlighting.. fixed.

  11. #11
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hummm so you got it
    The problem was in WHERE Clause peranthesis....
    WHERE (((dbo_Bezetting.Voorstellingsnummer)=1) AND ((dbo_Bezetting.Voorstellingsnummer)=1))
    ) AS BS
    ON BS.Stoelnummer = S.Stoelnummer
    WHERE (U.Voorstellingsnummer = 1) AND (U.Uitvoeringsnummer = 1);
    But how I didn't see the 1st WHERE clause and see only the last WHERE clause which I already corrected in the last post

    Anyhow SQL server and Ms-Access have not much difference in the SQL Statement:

    Here is the list of SQL VS Ms-Access hope this make it clear!

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Odd, I write SQL statements in VBA without parens in WHERE clause and they work fine.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Quote Originally Posted by June7 View Post
    Odd, I write SQL statements in VBA without parens in WHERE clause and they work fine.
    Yeah, it's working without parenthesis, but if you miss anyone of them like in #11 you will get missing ) error:

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert Access query to SQL Server query
    By mkallover in forum Queries
    Replies: 2
    Last Post: 05-13-2011, 06:20 AM
  2. Linked SQL Server Table Problems
    By jalton in forum Import/Export Data
    Replies: 3
    Last Post: 02-13-2010, 12:52 AM
  3. Query problems
    By geoffishere in forum Forms
    Replies: 1
    Last Post: 02-09-2010, 12:43 PM
  4. Sql server query to MS access query
    By blazixinfo@yahoo.com in forum Queries
    Replies: 0
    Last Post: 07-07-2009, 08:12 AM
  5. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums