SQL query with left join syntax error

Question

I have a query that I am trying to run, however, I continue to receive a syntax error when I run it. Below is a query that gives me an 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 

It is strange 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 is Query1:

 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:

 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 that composes Query1 and Query2 and use them as subqueries, however it does not work at startup.

** EDIT:

Another thing is that this query also works:

 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 the left join of the subquery or the second query itself. Not really sure though.

The exact error I get: Syntax error in the request. Incomplete offer request.

The following 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!

Answer 1

I understood why this is not working! Although this does not explain why working with real query objects (Query1 and Query2) worked, this fixed the problem, so I am fine with it.

Grouping in the second subquery caused a problem. I deleted the grouping in the second subquery, and then performed the grouping in the final query, which is a collection of two subqueries. The following is a working request 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; 

Thus, in principle, Access cannot combine two subqueries, where one is an aggregate and the other is not. To fix this, make both subqueries non-aggregate queries, combine them and run the aggregate on the final query.