SQL QUERY SYNTAX HELP!!!!!!!!!

[lndebug]

Hi all. I have an MS SQL query. What is its access equivalent

Error I get Join expression not supported

SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON s.RoleId = m.roleId
AND s.PersonId = "251"
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder

 

[MGFoster]

Hi all. I have an MS SQL query. What is its access equivalent

Error I get Join expression not supported

SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON s.RoleId = m.roleId
AND s.PersonId = "251"
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access doesn't allow the ON comparison to be a literal - it must be a
column in the other table. You'll have to stick that comparison in the
WHERE clause.

SELECT *
FROM SETUP_MENU
INNER JOIN
(SELECT menuid
FROM SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON s.RoleId = m.roleId
WHERE (MenuParent IS NULL AND s.PersonId = "251")
OR MenuParent = 0
GROUP BY menuid
) AS A
ON itemId = A.menuid
ORDER BY MenuOrder

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd40xoechKqOuFEgEQKoXgCg30pqXSlJXZvCpzFNrK1M19KgMw0An3B+
kOCbXhGYye6LtHeLJM0t4kW/
=1V35
-----END PGP SIGNATURE-----