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

Sorry again, I maybe have found my mistake, so please don’t try to solve this for the moment. I have to stop working on it for today. I will get back to this tomorrow and tell you if it works.

Answer 3

Many thanks to all three of you. I had to change labels due to privacy concerns and due to using a different language in the original, so in reality it did not actually say ".Name", so this was'nt the reason behind it (which of course you couldn't know, so: sorry for that). The reasons why nothing works certainly have something to do with the rather clumsy way I put my problem to you.

HansV, what you wrote gives me a table where I have many empty lines in the column PersonResponsible although for every subdivision, there is just one employee in charge. There is also one first deputy for every subdivision. But some subdivisions do not have a second deputy (NULL). That is why I resorted to LEFT JOIN. I could see that you flipped everything else around as well so that it should work with RIGHT JOIN, but it did'nt.

So I changed a few things, and one of them nearly [EDIT - at first, I thought it had worked] did it for me: In what HansV wrote, I reversed the order of the parts beginning with "ON", so I started with the second deputy and ended with the person primarily responsible. This gave me a view of everything except those Subdivisions that do not have a second deputy, but I would like to see them as well like in SQLite.

ImbHB, I tried yours as well, but it gave me a syntax error. I actually made a mistake in not telling you (via code) that FirstDeputy and SecondDeputy belong to the Subdivisions table, the employee table only consists of ID and name. I tried to fix that myself, but I did'nt succeed. The highlighting in Access 2016 is useless for a beginner like me - it marks a part of the code as wrong that works when I use HansV's lines.

Thanks for taking the time to help me, it is greatly appreciated!

Answer 4

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;

Hi Chris,

I always use as many parentheses as there are Joins.

In the Joins I specify both tablename and fieldname, for easy check of the ON clause with respect to the right tables.

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

FROM (Subdivisions

INNER JOIN ((Employees

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

LEFT JOIN Employees as Employees2 On Employees.SecondDeputy = Employees2.EmployeeID)

ON Subdivision.PersonResponsible=Employees.EmployeeID);

Using this concept I can completely automate the building of dynamical sql-strings depending of the current context.

In your Employees table you use a field "Name". As Name is a reserved word, you can better use a different fieldname, as "Last_name" of "Full_name".

Imb.

Answer 5

The first SQL has

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