Avatar of vesnahu
vesnahu asked on

Access query syntax error

I run the following from SQL2005 and got "query executed successfully" but when I run in Access query, I got Syntax error (missing operator)??? please help me with "(" and ")" in FROM, thanks!

SELECT MisToolPockets.Resource, MisToolPockets.ToolPocket, MisToolPockets.ToolDescription, ToolComponents.Component, ToolComponents.ComponentDescription, ToolComponents.InsertRad, ToolComponents.InsertGrade, SpareParts.SparePart, SpareParts.SparePartDescription
FROM Mis left JOIN MisToolPockets ON Mis.Resource = MisToolPockets.Resource left JOIN MisToolPocketComponents ON MisToolPockets.Resource = MisToolPocketComponents.Resource AND MisToolPockets.ToolPocket = MisToolPocketComponents.ToolPocket left JOIN ToolComponents ON MisToolPocketComponents.ComponentRn = ToolComponents.ComponentRn left JOIN ToolComponentSpareParts ON ToolComponents.ComponentRn = ToolComponentSpareParts.ComponentRn left JOIN SpareParts ON ToolComponentSpareParts.SparePartRn = SpareParts.SparePartRn;
Microsoft AccessSQL

Avatar of undefined
Last Comment
vesnahu

8/22/2022 - Mon
Thomasian

hi vesnahu,

When joining more than 2 tables in access, you need to add some more parentheses.
SELECT MisToolPockets.Resource, MisToolPockets.ToolPocket, MisToolPockets.ToolDescription, ToolComponents.Component, ToolComponents.ComponentDescription, ToolComponents.InsertRad, ToolComponents.InsertGrade, SpareParts.SparePart, SpareParts.SparePartDescription
FROM ((((Mis left JOIN MisToolPockets ON Mis.Resource = MisToolPockets.Resource) left JOIN MisToolPocketComponents ON MisToolPockets.Resource = MisToolPocketComponents.Resource AND MisToolPockets.ToolPocket = MisToolPocketComponents.ToolPocket) left JOIN ToolComponents ON MisToolPocketComponents.ComponentRn = ToolComponents.ComponentRn) left JOIN ToolComponentSpareParts ON ToolComponents.ComponentRn = ToolComponentSpareParts.ComponentRn) left JOIN SpareParts ON ToolComponentSpareParts.SparePartRn = SpareParts.SparePartRn;

Open in new window

Mark Wills

have a look at ...


SELECT MisToolPockets.Resource, MisToolPockets.ToolPocket, MisToolPockets.ToolDescription, ToolComponents.Component, ToolComponents.ComponentDescription, ToolComponents.InsertRad, ToolComponents.InsertGrade, SpareParts.SparePart, SpareParts.SparePartDescription
FROM (((((Mis
left JOIN MisToolPockets ON Mis.Resource = MisToolPockets.Resource )
left JOIN MisToolPocketComponents ON MisToolPockets.Resource = MisToolPocketComponents.Resource AND MisToolPockets.ToolPocket =MisToolPocketComponents.ToolPocket )
left JOIN ToolComponents ON MisToolPocketComponents.ComponentRn = ToolComponents.ComponentRn )
left JOIN ToolComponentSpareParts ON ToolComponents.ComponentRn = ToolComponentSpareParts.ComponentRn )
left JOIN SpareParts ON ToolComponentSpareParts.SparePartRn = SpareParts.SparePartRn);
 
ASKER
vesnahu

Thomasian, thanks for your quick response, I tried yours and got the following

Syntax error (missing operator) in query expression 'MisToolPocketComponents.ComponentRn = ToolComponents.ComponentRn left JOIN ToolComponentSpareParts ON ToolComponents.ComponentRn = ToolComponentSpareParts.ComponentRn'.

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
vesnahu

Thanks for your help!