Joining multiple subqueries

Question

I'm trying to use several select queries to merge multiple WHERE clauses on the same data into 1 query. It works when I join 1 sub query but once I try to join a 2nd, I get a syntax error that I'm having difficulty working through. Any help would be greatly appreciated!

SELECT [CONSULTATION].Theatre
  ,[CONSULTATION].[Order Type]
  ,[CONSULTATION].[Service Type]
  ,[CONSULTATION].[ORDERS]
  ,[CONSULTATION].[CONSULTATION DAYS]
  ,[DESIGN].[DESIGN DAYS]
FROM (
  SELECT [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
    ,Count([Order Metrics].[Order ID]) AS ORDERS
    ,ROUND(AVG(([Order Metrics].HOURS) / 24), 1) AS [CONSULTATION DAYS]
  FROM [Order Metrics]
  WHERE (
      (([Order Metrics].[Order Status]) = "closed")
      AND ((DateDiff("d", [Order Metrics] ! [Date Created], Now())) < 366)
      AND (
        (
          [ORDER METRICS] ! FROM
          ) = "CONSULTATION"
        )
      )
  GROUP BY [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
  HAVING LEN([ORDER METRICS].[THEATRE]) > 1
  ) AS CONSULTATION
INNER JOIN (
  SELECT [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
    ,Count([Order Metrics].[Order ID]) AS ORDERS
    ,ROUND(AVG(([Order Metrics].HOURS) / 24), 1) AS [DESIGN DAYS]
  FROM [Order Metrics]
  WHERE (
      (([Order Metrics].[Order Status]) = "closed")
      AND ((DateDiff("d", [Order Metrics] ! [Date Created], Now())) < 366)
      AND (
        (
          [ORDER METRICS] ! FROM
          ) = "DESIGN"
        )
      )
  GROUP BY [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
  HAVING LEN([ORDER METRICS].[THEATRE]) > 1
  ) AS DESIGN ON ([CONSULTATION].Theatre = [DESIGN].THEATRE)
  AND ([CONSULTATION].[Order Type] = [DESIGN].[ORDER TYPE])
  AND ([CONSULTATION].[Service Type] = [DESIGN].[SERVICE TYPE])
INNER JOIN (
  SELECT [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
    ,Count([Order Metrics].[Order ID]) AS ORDERS
    ,ROUND(AVG(([Order Metrics].HOURS) / 24), 1) AS [QUOTE REQUEST DAYS]
  FROM [Order Metrics]
  WHERE (
      (([Order Metrics].[Order Status]) = "closed")
      AND ((DateDiff("d", [Order Metrics] ! [Date Created], Now())) < 366)
      AND (
        (
          [ORDER METRICS] ! FROM
          ) = "QUOTE REQUEST"
        )
      )
  GROUP BY [Order Metrics].Theatre
    ,[Order Metrics].[Order Type]
    ,[Order Metrics].[Service Type]
  HAVING LEN([ORDER METRICS].[THEATRE]) > 1
  ) AS [QUOTE REQUEST] ON ([CONSULTATION].Theatre = [QUOTE REQUEST].THEATRE)
  AND ([CONSULTATION].[Order Type] = [QUOTE REQUEST].[ORDER TYPE])
  AND ([CONSULTATION].[Service Type] = [QUOTE REQUEST].[SERVICE TYPE]);

Please hit the edit link under your question, select highlight your queries and press the {} button above the text box to format them as code. Thanks — Caius Jard, Dec 08 '18 at 04:15
If you could make some attempt to format/indent it it would go a long way too. Access generates dogs dinner queries, it's true but that doesn't necessarily mean that other people are going to want to wade through it if it's just splurged on a page. Might be handy for you to describe what you're trying to do/what tables you have/data they contain and what end result you want rather than saying "help fix this? (Splurge)" and leaving is to work out the tables, data, conditions etc. Welcome to the site! — Caius Jard, Dec 08 '18 at 04:21
To format SQL-Statements use a SQL-Formatter like [sqlinform](https://www.sqlinform.com/online-sql-formatter/)! — ComputerVersteher, Dec 08 '18 at 04:33
What is the error message? Using the Access query designer or trying to freehand in SQL View? — June7, Dec 08 '18 at 05:09
Possible duplicate of [SQL multiple join statement](https://stackoverflow.com/questions/7854969/sql-multiple-join-statement) — Andre, Dec 09 '18 at 09:35

score 0 · Answer 1 · answered Dec 09 '18 at 09:27

0

Have you considered saving each query separately, checking that they have the same number of columns with the same data type, then pulling them all together with a union query? I find this much more maintainable...

answered Dec 09 '18 at 09:27

trevor

257
3
9

Joining multiple subqueries

1 Answers1