SQL multiple joins

Question

Obtaining a syntax error with the script below in MS access. Works fine without the second inner join. Any ideas?

SELECT 
   tblStudySetup.[Room Number], 
   qQC1A.[% of Hr Per Doc] AS [QC1A]
   qQC1R.[% of Hr Per Doc] AS [QC1R]
FROM tblStudySetup 

INNER JOIN (
SELECT tblStudySetup.[Room Number], tblMultipler.[% of Hr Per Doc]
FROM tblStudySetup LEFT JOIN tblMultipler 
ON tblStudySetup.QC1AType = tblMultipler.Type
)  AS qQC1A 
ON tblStudySetup.[Room Number] = qQC1A.[Room Number]

INNER JOIN (
SELECT tblStudySetup.[Room Number], tblMultipler.[% of Hr Per Doc]
FROM tblStudySetup LEFT JOIN tblMultipler 
ON tblStudySetup.QC1RType = tblMultipler.Type
)  AS qQC1R 
ON tblStudySetup.[Room Number] = qQC1R.[Room Number];

Table structure as follows

tblStudySetup: [Room Number], [QC1A], [QC1R]

tblMultipler: [Type], [% of Hr Per Doc]

So the tblStudySetup per room would have a multiplier type say "QC1A-default" which could be equal to a [% of Hr Per Doc] of say 20%. There are a few different types of multiplers.

If you load the SQL into SQL View of a query and try to run it, Access should go to the part of the query that is causing the syntax error — dbmitch, Jun 28 '16 at 15:37
it does but it doesn't seem to say anything useful. Tempted to use a grouped union instead to avoid these multiple joins — Jake Duddy, Jun 28 '16 at 15:43
the joins in the subqueries are using QC1AType and QC1RType but these columns are not present in the tblStudySetup — Jayvee, Jun 28 '16 at 16:03
[Access requires parentheses in the FROM clause for queries which include more than one join.](http://stackoverflow.com/a/20929533/77335) — HansUp, Jun 28 '16 at 16:11

score 1 · Answer 1 · answered Jun 28 '16 at 15:43

1

There is a comma missing in your original select statement, between your second and third fields.

Corrected:

SELECT 
   tblStudySetup.[Room Number], 
   qQC1A.[% of Hr Per Doc] AS [QC1A],
   qQC1R.[% of Hr Per Doc] AS [QC1R]
FROM tblStudySetup

answered Jun 28 '16 at 15:43

L Robin

55
7

That's just a copy paste error, but still not working get the following error message. – Jake Duddy Jun 28 '16 at 15:46
Syntax error (missing operator) in query express 'tblStudySetup.[Room Number] = qQC1A.[Room Number]. Without the second inner join paragraph this section works though – Jake Duddy Jun 28 '16 at 15:47
1

A quick search suggests you may need to add parentheses around each join: http://stackoverflow.com/questions/15389553/syntax-error-missing-operator-in-query-expression I haven't used MS Access actively for some time, but I do remember it was picky about joins syntax. – L Robin Jun 28 '16 at 15:51

SQL multiple joins

1 Answers1