LEFT JOIN multiple sub queries

Question

My intention is to create a table with the number of orders and revenue for two different years for every product. My idea was to use a double left Join but I don't understand why this query does not work on MS ACCESS. I decided to use LEFT JOIN because in the subquery per year some products may not appear if it was not ordered. The Subqueries a and b are OK. They work separately. What's the problem with this query?

SELECT p.PK_Product_ID, a.[2013_Orders], a.[2013_Gross_Value], b.[2014_Orders], b.[2014_Gross_Value]
    FROM Products AS p

LEFT JOIN 
     (SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders,  
     SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
     FROM [Order Details] AS od, p, Orders AS o
     WHERE od.FK_Product_ID = p.PK_Product_ID
     AND od.FK_Order_ID = o.PK_Order_ID
     AND YEAR(o.OrderDate) = 2013
     GROUP BY p.PK_Product_ID) AS a

ON p.PK_Product_ID = a.Product_ID

LEFT JOIN 
    (SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2014_Orders,  SUM((p.UnitPrice*od.Quantity)) AS 2014_Gross_Value
    FROM od, p, o
    WHERE od.FK_Product_ID = p.PK_Product_ID
    AND od.FK_Order_ID = o.PK_Order_ID
    AND YEAR(o.OrderDate) = 2014
    GROUP BY p.PK_Product_ID) AS b
 ON p.PK_Product_ID = b.Product_ID;

Answer 1

You can do it with conditional aggregation:

SELECT p.PK_Product_ID AS Product_ID,
       SUM(IIF(YEAR(o.OrderDate) = 2013, 1, 0)) AS 2013_Orders,
       SUM(IIF(YEAR(o.OrderDate) = 2013, p.UnitPrice * od.Quantity, 0)) AS 2013_Gross_Value,
       SUM(IIF(YEAR(o.OrderDate) = 2014, 1, 0)) AS 2014_Orders,
       SUM(IIF(YEAR(o.OrderDate) = 2014, p.UnitPrice * od.Quantity, 0)) AS 2014_Gross_Value
FROM (Products AS p LEFT JOIN [Order Details] AS od ON od.FK_Product_ID = p.PK_Product_ID)
LEFT JOIN (SELECT * FROM Orders WHERE YEAR(OrderDate) IN (2013, 2014)) AS o ON od.FK_Order_ID = o.PK_Order_ID
GROUP BY p.PK_Product_ID

If you want only the products ordered in the years 2013 and/or 2014 then you can change the LEFT joins to INNER joins.