Problems with Parentheses and SQL JOIN Statements in Access 2016 Professional

Question

Hello there,

I wrote a query which works like a charm in SQLite:

SELECT Subdivision, Employees.Name, Employees1.Name AS fsDep, Employees2.Name AS secDep FROM Subdivisions INNER JOIN Employees ON PersonResponsible=Employees.EmployeeID LEFT JOIN Employees as Employees1 ON FirstDeputy=Employees1.EmployeeID LEFT JOIN Employees as Employees2 ON SecondDeputy=Employees2.EmployeeID;

As I have only a question regarding syntax, I will not bore you with the details of the database (if you need to know them to help me, please tell me so).

I have learnt that Access needs parentheses to grasp multiple join statements, but what I found on the net will not work ("JOIN Expression not supported"):

SELECT Subdivision, Employees.Name, Employees1.Name AS fsDep, Employees2.Name AS secDep

FROM (((Subdivisions

INNER JOIN Employees ON PersonResponsible=Employees.EmployeeID)

LEFT JOIN Employees as Employees1 ON FirstDeputy=Employees1.EmployeeID)

LEFT JOIN Employees as Employees2 ON SecondDeputy=Employees2.EmployeeID);

Cutting the first and the last "(" und ")" leads to the same result.

This alternative does not work either:

SELECT Subdivision, Employees.Name, Employees1.Name AS fsDep, Employees2.Name AS secDep

FROM Subdivisions

INNER JOIN Employees

(LEFT JOIN Employees as Employees1

(LEFT JOIN Employees as Employees2

ON SecondDeputy=Employees2.EmployeeID)

ON FirstDeputy=Employees1.EmployeeID)

ON PersonResponsible=Employees.EmployeeID;

Thanks for helping me out!

Answer 1

select * from Employees;

EmployeeID	EmployeeName
1	Jim
2	Tim
3	Kim
4	Wim

select * from Subdivisions;

Subdivision	PersonResponsible	FirstDeputy	SecondDeputy
B1	1	1	3
A1	2	3	4
C2	3	4	2
C1	4	1	2
B2	3	2

select (select distinct EmployeeName from Employees where EmployeeID= a.PersonResponsible) as PersonResponsible, (select distinct EmployeeName from Employees where EmployeeID= a.FirstDeputy) as FirstDeputy, (select distinct EmployeeName from Employees where EmployeeID= a.SecondDeputy) as SecondDeputy from Subdivisions as a;

PersonResponsible	FirstDeputy	SecondDeputy
Jim	Jim	Kim
Tim	Kim	Wim
Kim	Wim	Tim
Wim	Jim	Tim
Kim	Tim

rename field name must add

as

in dao

http://anyoupin.cn/bsbm/stu60/sqlDao/sqlEditor\_dao.html

online test tool

select * from Employees;

select * from Subdivisions;

select (select distinct EmployeeName

from Employees where EmployeeID= a.PersonResponsible) as PersonResponsible, (select distinct EmployeeName from Employees where EmployeeID= a.FirstDeputy) as FirstDeputy, (select distinct EmployeeName from Employees where EmployeeID= a.SecondDeputy) as SecondDeputy from Subdivisions as a;

Answer 2

Hans, That's what I suspected, but I wanted to make sure.

Answer 3

@Scottgem: Employees1 and Employees2 are aliases of Employees. Their Subdivisions table has three links to Employees: one for PersonResponsible, one for FirstDeputy and one for SecondDeputy.

Answer 4

Does this work?

SELECT SubDivisions.Subdivision, Employees.[Name], Employees1.[Name] AS fsDep, Employees2.[Name] AS secDep FROM
Employees RIGHT JOIN (Employees AS Employees1 RIGHT JOIN (Employees AS Employees2 INNER JOIN Subdivisions ON Employees2.EmployeeID = Subdivisions.PersonResponsible) ON Employees1.EmployeeID = Subdivisions.FirstDeputy) ON Employees.EmployeeID = Subdivisions.SecondDeputy;

Answer 5

If you try switching to Design mode, if there are errors in syntax, those errors will be highlighted in SQL View.

Also Name is a reserved word in Access and shouldn't be used as an Object Name.

Also do you actually have tables named Employees, Employees1 and Employees2? What are the purposes of those?