Convert a SQL query to MS-Access SQL

Question

Just trying to get my SQL query to work in MS-Access but it doesn't want to. I tried to find a guide or something about this, because I know it happens a lot of time and it's often just about adding some parenthesis, but I haven't been able to get this query working despite all my tries:

SELECT t1.Name AS lev1, t2.Name as lev2, t3.Name as lev3, t4.Name as lev4
FROM Folder AS t1
LEFT JOIN Folder AS t2 ON t2.Parent = t1.ObjId 
LEFT JOIN Folder AS t3 ON t3.Parent = t2.ObjId 
LEFT JOIN Folder AS t4 ON t4.Parent = t3.ObjId 
WHERE t1.ObjId = '123456789';

The usual Access Syntax Error - No operator and then it points out to t2.Name on first line. Not sure why you ask me this. As you probably already know, Access needs a lot of parenthesis and that's why it's a syntax error, but I don't know where to put the parenthesis. I never exactly understood the difference and that's why I'm asking for some kind of guidelines or indications about the differences. — dan
– dan, Commented Mar 5, 2013 at 15:50

Taryn · Accepted Answer · 2013-03-05 15:52:47Z

4

MS Access requires parentheses around each join. When you have multi-table joins, you will have several:

SELECT t1.Name AS lev1, t2.Name as lev2, t3.Name as lev3, t4.Name as lev4
FROM ((Folder AS t1
LEFT JOIN Folder AS t2 ON t2.Parent = t1.ObjId)
LEFT JOIN Folder AS t3 ON t3.Parent = t2.ObjId)
LEFT JOIN Folder AS t4 ON t4.Parent = t3.ObjId 
WHERE t1.ObjId = '123456789';

Formatted it looks like this:

SELECT t1.Name AS lev1, t2.Name as lev2, t3.Name as lev3, t4.Name as lev4
FROM 
(
    (
        Folder AS t1
        LEFT JOIN Folder AS t2 ON t2.Parent = t1.ObjId
    )
    LEFT JOIN Folder AS t3 ON t3.Parent = t2.ObjId
)
LEFT JOIN Folder AS t4 ON t4.Parent = t3.ObjId 
WHERE t1.ObjId = '123456789';

answered Mar 5, 2013 at 15:52

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dan Over a year ago

This does work. Is this always the same? All the opening parenthesis at the beginning of the FROM clause and then close one after each JOIN/ON statement?

Taryn Over a year ago

@dnLL yes that is the syntax. Place the opening parentheses at the FROM and the closing after each join.

Collectives™ on Stack Overflow

Convert a SQL query to MS-Access SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related