Works on MS-SQL not Access (1 Viewer)

[kevlray]

We have an Access (2007) DB with a MS-SQL (2008) back end. I was struggling in Access to get the results I wanted, so I switched over to MS-SQL and got a query to work. When I bring the query over to Access (Create, Query Design, SQL). When I try to execute it, I get a syntax error in From clause. Basically I am trying to list all the employees that have not attended the ' Performance Management and MOC Redesign' training. The aliased MTbl_Employee tables are just to bring other supervisors names in. I tried a sub-query as a table and Access did not like that either.

select distinct
Mtbl_Employees.[Employee#]
, Mtbl_Employees.EmployeeName as Supervisor
, AD.EmployeeName as ADEmpNm
, DM.EmployeeName as DMEmpNm
from MTbl_Employees


join MTbl_Positions on MTbl_Positions.[Pos#] = MTbl_Employees.[Pos#]
left join MTbl_Employees as AD on AD.[Pos#] = Mtbl_Positions.[ADPos#]
left join MTbl_Employees as DM on DM.[Pos#] = Mtbl_Positions.[DMPos#]
where
Mtbl_Employees.Sup = 1
and
Not exists (select [Employee#]
from Etbl_Training
where Training = 'Performance Management and MOC Redesign'
and MTbl_Employees.[Employee#] = ETbl_Training.[Employee#])
order by [Employee#]

Any suggestions would be appreciated.

 

[Galaxiom]

Access requires multiple joins to be bracketed.

from 
(( MTbl_Employees 
join MTbl_Positions on MTbl_Positions.[Pos#] = MTbl_Employees.[Pos#]
) left join MTbl_Employees as AD on AD.[Pos#] = Mtbl_Positions.[ADPos#]
) left join MTbl_Employees as DM on DM.[Pos#] = Mtbl_Positions.[DMPos#]

BTW I would strongly advise you to desist from using special characters in object names.

 

[kevlray]

Thanks for the advise on the brackets. I inherited this database, so I am stuck with the field names.