SQL sub query with left join syntax error

Question

I have a query I am attempting to run however I keep getting a syntax error when running it. Below is the query that is giving me the error:

SELECT A.*, B.Total AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS       Priority, tblProcesses.minutesPerItem AS Minutes
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
    WHERE tblProcesses.ID=27
) AS A
LEFT JOIN 
(
    SELECT tblWorkflow.assignedTo AS Employee, Sum(IIf(DateDiff('d',[openedDate],Date())=0,[itemCount]*[minutesPerItem],0)) AS Total
    FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
    WHERE tblWorkflow.processName=27 AND (tblWorkflow.Status=1 Or tblWorkflow.Status=2)
    GROUP BY tblWorkflow.assignedTo
) AS B 
ON A.Employee = B.Employee
ORDER BY B.Total, A.Employee

The weird thing about this is that the following query works fine:

SELECT Query1.*, Query2.Total AS Assigned
FROM Query1 LEFT JOIN Query2 ON Query1.Employee = Query2.Employee
ORDER BY Query2.Total, Query1.Employee

Where Query1 is:

SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes
FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
WHERE tblProcesses.ID=27;

and Query2 is:

SELECT tblWorkflow.assignedTo AS Employee, Sum(IIf(DateDiff('d',[openedDate],Date())=0,[itemCount]*[minutesPerItem],0)) AS Total
FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
WHERE tblWorkflow.processName=27 AND (tblWorkflow.Status=1 Or tblWorkflow.Status=2)
GROUP BY tblWorkflow.assignedTo;

All I did was take the code making up Query1 and Query2 and used them as sub queries however it does not work when ran.

**EDIT:

One more thing is that this query works as well:

SELECT A.*, Query2.Total AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblEvents.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName
    WHERE tblProcesses.ID=27
) AS A
LEFT JOIN Query2 
ON A.Employee = Query2.assignedTo
ORDER BY Query2.Total, A.Employee

Which makes me think that the error has something to do with either the left join of a sub query or the second query itself. Not really sure though.

The exact error that I get is: Syntax error in query. Incomplete query clause.

Below are the tables:

tblEvents

ID  eventName                eventAbbr  deskName    Notes 
1   Partial Call             PC         Redemption   
2   Full Call                FC         Redemption   
3   Maturity                 MAT        Redemption   
4   UIT Final Payment        UFNL       Redemption   
5   Prerefunding/Defeasement PRF/DEF    Redemption   
6   UIT Distribution         UIT        Dividend     
7   Equity Distribution      EQTY       Dividend

tblProcesses

ID  processName             processAbbr     eventName                               minutesPerItem Priority assignAll   dataSource      Notes
17  DRV Coding (Account)    DRVCDA          UIT Distribution                        0.025                   No          Document Direct 
18  DRV Buys                DMBUY           Equity Distribution                     0.35           !        Yes         Document Direct 
19  Liquidation Exceptions  LIQEXC          Equity Distribution                     0.085                   Yes         Document Direct 
20  CMV                     CMV             UIT Distribution, Equity Distribution   30                      No          BMC Jobs    
21  As of Trades            ASOFTR          UIT Distribution, Equity Distribution   5              !        No          Document Direct 
22  Dividend Exceptions     DVEXC           Equity Distribution                     1                       No          Document Direct 
23  DRV Buys (Previous Day) PRVBUY          Equity Distribution                     0.15                    Yes         Document Direct 
24  DRV Coding (Security)   DRVCDS          Equity Distribution                     10                      No          Document Direct 
25  Upcoming Elections      UPCMEL          Equity Distribution                     5              !        No          Document Direct 
26  Long Short              LNGSHRT         UIT Distribution, Equity Distribution   2                       No          SQL Server  
27  Sold Lottery Shares     SLDLTSH         Partial Call                            1                       No          Document Direct 
28  Upcoming Finals         UPCMFN          UIT Final Payment                       1              !        No          Document Direct 
29  Remove Inventory        RMVINV          UIT Final Payment                       0.5                     No          Document Direct 

tblSkillSets

employeeName    deskName    eventName
300             Dividend    Equity Distribution
300             Dividend    UIT Distribution
305             Dividend    UIT Distribution
305             Dividend    Equity Distribution
342             Redemption  Full Call
342             Redemption  Maturity
342             Redemption  Partial Call
342             Redemption  UIT Final Payment
342             Redemption  Prerefunding/Defeasement
345             Redemption  Maturity
345             Redemption  Full Call
345             Redemption  UIT Final Payment
345             Redemption  Partial Call
345             Redemption  Prerefunding/Defeasement
346             Dividend    UIT Distribution
346             Dividend    Equity Distribution
348             Redemption  Partial Call
348             Redemption  Full Call
348             Redemption  Maturity
348             Redemption  UIT Final Payment
348             Redemption  Prerefunding/Defeasement
350             Dividend    UIT Distribution
350             Dividend    Equity Distribution

tblWorkflow

ID      assignedTo  eventName   processName         itemCount   estimatedMinutes    Status  Priority    openedBy    openedDate
233382  348         Maturity    Maturity Exceptions 8           24                  3                   SYSTEM      1/9/2018 3:20:30 PM
233383  345         Maturity    Maturity Exceptions 8           24                  3                   SYSTEM      1/9/2018 3:20:30 PM
233384  342         Maturity    Maturity Exceptions 7           21                  3                   SYSTEM      1/9/2018 3:20:30 PM

Expected Result

Any help is appreciated, thanks!

Gorham

Unfortunatly, yes. The way I have the database set up I had to use a multi value field. – Gorham

Thanks, yea I'm aware of that but that still doesn't explain why the query that uses the query objects works. The query objects themselves still contain the multi valued fields and it works okay. – Gorham

Not to mention that the query which seems to be causing the error (Query2) doesn't even use a multi value field in it's statement. – Gorham

Technically the employeeName field is the ID, it displays the name associated with the ID but is bound by the ID itself. – Gorham

Can you show tblWorkflow as well? – Phosphate

We need tblWorkflow. Also, instead of screenshot, can you copy and paste directly from MS Access to StackOverflow. We will adjust format if you cannot. – Depredate

@Gorham I've duplicated your tables and query, but it does not run because in the query you have a lot of type mismatches, and I'm not sure how your subqueries can even run by themselves. Two instances: tblEvents.ID = tblSkillSets.eventName as the join criteria in the first subquery and tblProcesses.ID = tblWorkflow.processName as the join criteria in the second subquery. In both cases you are trying to compare numbers (ID columns) to strings (eventName and processName). Can you clarify this? – Phosphate

I apologize I should have explained that in the op. The name fields in both scenarios are bound in the table by the ID so the value itself is technically a number however the string is displayed. I just don't like linking string fields together but like to actuallly see them when viewing the table, it's just easier for me to view. – Gorham

Not all SQL statements can be nested. This may be such a case. – Postmortem

It's possible but I just don't see the difference between that and using the query objects themselves. – Gorham

You have given much helpful info but please read & act on minimal reproducible example. Please use text, not images/links, for text (including tables). – Laszlo

@Gorham if that's the case then you need to show that. I went through the trouble to duplicate your scenario and couldn't run the query because you are not displaying the data correctly. Please show us exactly what the query is operating on so that it can be reproduced. – Phosphate

Answer 1

I figured out why this wasn't working! Although it doesn't explain why using the actual query objects (Query1 and Query2) worked, it fixed me problem so I'm okay with it.

The grouping in the second sub-query was causing the issue. I removed the grouping in the second sub-query and then performed the grouping in the final query which is the aggregate of the two sub queries. Below is the working query that resolved the issue:

SELECT A.Employee, A.Event, A.Process, A.Priority, A.Minutes, Sum(B.Total) AS Assigned
FROM (
    SELECT tblSkillSets.employeeName AS Employee, tblSkillSets.eventName AS Event, tblProcesses.ID AS Process, tblProcesses.Priority.Value AS Priority, tblProcesses.minutesPerItem AS Minutes 
    FROM tblEvents INNER JOIN (tblSkillSets INNER JOIN tblProcesses ON tblSkillSets.eventName = tblProcesses.eventName.Value) ON tblEvents.ID = tblSkillSets.eventName 
    WHERE tblProcesses.ID=27
) AS A 
LEFT JOIN (
    SELECT tblWorkflow.assignedTo, [itemCount]*[minutesPerItem] AS Total
    FROM tblProcesses RIGHT JOIN tblWorkflow ON tblProcesses.ID = tblWorkflow.processName
    WHERE (tblWorkflow.Status=1 Or tblWorkflow.Status=2) 
    AND DateDiff('d',[openedDate],Date())=0
) AS B 
ON A.Employee = B.assignedTo
GROUP BY A.Employee, A.Event, A.Process, A.Priority, A.Minutes, A.Employee
ORDER BY Sum(B.Total), A.Employee;

So basically Access cannot combine two sub queries where one is an aggregate and the other is not. In order to fix this make both sub queries non-aggregate queries, combine them, and perform the aggregate on the final query.

Gorham