Solved Crosstab query

[Noel96334]

Hi,
I have a crosstab query that shows the sales pro month. That's works well, but I want to show
all products and not only those who have a value.
How can I do that ? Is there anyone who can help me with this ?
Thanks.

 

[ebs17]

You would need to use OUTER JOIN instead of INNER JOIN in some places, like (for qryDataCrosstab):

SELECT
   C.CategoryOrder,
   P.ProductName,
   OD.Quantity,
   O.OrderDate
FROM
   (tblOrders AS O
      RIGHT JOIN
         (tblProducts AS P
            LEFT JOIN tblOrderDetails AS OD
            ON P.ProductID = OD.ProductID
         )
      ON O.OrderID = OD.OrderID
   )
   INNER JOIN tblCategory AS C
   ON P.CategoryID = C.CategoryID

 

[Noel96334]

Thanks for the reply. I found the solution.

SELECT tblCategory.CategoryOrder, tblProducts.ProductName, Sum(tblOrderDetails.Quantity) AS SomVanQuantity, tblOrderDetails.OrderID, tblCategory.CategoryName
FROM (tblCategory INNER JOIN tblProducts ON tblCategory.CategoryID = tblProducts.CategoryID) LEFT JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID
GROUP BY tblCategory.CategoryOrder, tblProducts.ProductName, tblOrderDetails.OrderID, tblCategory.CategoryName
ORDER BY tblCategory.CategoryOrder, tblProducts.ProductName;