Syntac error (missing operator)

[Mark]

Hi - I can get this to work in SQL Server - but when also trying to make
the application compatible with MS Access I get an error:

Select tblfaqnetgroups .group_name from tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

The error in Access is:

Syntax error (missing operator) in query expression
'tblfaqnetroles .group_id = tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id'

Any help would be much appreciated,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Mark" <an*******@devd ex.com> wrote in message
news:3f******** *************@n ews.frii.net...

Hi - I can get this to work in SQL Server - but when also trying to make
the application compatible with MS Access I get an error:

Select tblfaqnetgroups .group_name from tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

The error in Access is:

Syntax error (missing operator) in query expression
'tblfaqnetroles .group_id = tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id'

Any help would be much appreciated,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This is more of an Access question, but it seems that Access requires
parentheses:

Select tblfaqnetgroups .group_name from ( tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id )
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

This syntax appears to work in MSSQL also, but the parentheses look
confusing (at least to me), as they suggest a derived table where there
isn't one.

Simon

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

This is more of an Access question, but it seems that Access requires
parentheses:

Select tblfaqnetgroups .group_name from ( tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id )
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

This syntax appears to work in MSSQL also, but the parentheses look
confusing (at least to me), as they suggest a derived table where there
isn't one.

While I agree that the parentheses here are only white noice, there
are cases where you need them:
SELECT ...
FROM a
LEFT JOIN (b JOIN c ON b.col = c.col) ON a.col = b.col

Here, you (logically) first join b and c, and then you to an outer
join between a and this result.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

Simon Hayes (sq*@hayes.ch) writes:

This is more of an Access question, but it seems that Access requires
parentheses:

Select tblfaqnetgroups .group_name from ( tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id )
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

This syntax appears to work in MSSQL also, but the parentheses look
confusing (at least to me), as they suggest a derived table where there
isn't one.

While I agree that the parentheses here are only white noice, there
are cases where you need them:
SELECT ...
FROM a
LEFT JOIN (b JOIN c ON b.col = c.col) ON a.col = b.col

Here, you (logically) first join b and c, and then you to an outer
join between a and this result.

Thanks for the clarification - I didn't mention this since the
original post is an inner join, but that may have misled the original
poster (hopefully not).