MS Access Query - Display All Rows from Original Joined Table-CodePudding

I need to write a query that displays ALL revenue centers for a month whether they have revenue or not. This seems like a simple request but I have seemed to hit a brick wall. Below is my SQL:

SELECT ID_ItemNominal, ItemNominal_Description, Sum(Nz([ITM_Net],0)) AS ITM_Net_Total
FROM TSub_ItmNominal LEFT JOIN (T_Invoice RIGHT JOIN T_LineItems ON T_Invoice.ITM_Reference = T_LineItems.ITM_Reference) ON TSub_ItmNominal.ID_ItemNominal = T_LineItems.ITM_Nominal
WHERE (((Year([ITM_Date]))=[report_year] Or (Year([ITM_Date])) Is Null) AND ((Month([ITM_Date]))=[report_month]))
GROUP BY TSub_ItmNominal.ID_ItemNominal, TSub_ItmNominal.ItemNominal_Description
HAVING (((TSub_ItmNominal.ID_ItemNominal) Like "4*"))
ORDER BY TSub_ItmNominal.ID_ItemNominal;

ID_ItemNominal = the Integer code for the Revenue Center
ItemNominal_Description = the description of the Revenue Center
ITM_Net = the Currency amount for the Line Item on the Invoice, to be SUM for a month total
ITM_ Date = the Date of the Invoice

My thought was to use the LEFT JOIN to say that I want to see ALL of the revenue centers, even if those records do not have any data for that month. What I get is the centers that DO have revenue for the year but DO NOT have revenue for the month are not shown / filtered out.

What the Current query provides:

40500 | Sales - Digital | $###.##
40700 | Sales - Misc    | $###.##
40800 | Sales - Mail    | $###.##
40900 | Sales - Clothing| $0.00

We have not done any revenue under 40900 this year so far so it shows as a result in the query. We have done revenue in 40600 this year but not for the month of April. The 40600 seems to be filtered out by the WHERE part of the query as well as any other revenue centers that we have revenue for the year but not the selected date.

I would like to see these revenue centers included in the query but show as $0.00 for the month.

Any help would be greatly appreciated, I feel like I am close but I just can't seem to get the correct results. Thank you in advance!

CodePudding user response：

Usually, when you run LEFT JOIN WHERE you run an analogous INNER JOIN. But according to your specifications, the sub items table should be optionally joined since it can contain actual sales data not exhaustive of all revenue centers.

Therefore, run the WHERE filtering within a subquery and then have this subquery left joined to main set of revenue centers. Also, for readability below converts RIGHT JOIN to LEFT JOIN and uses table aliases instead of full table names.

SELECT main.ITM_Nominal, 
       main.ItemNominal_Description, 
       SUM(NZ(sub.[ITM_Net], 0)) AS ITM_Net_Total

FROM (T_LineItems AS main 
LEFT JOIN T_Invoice AS inv 
   ON inv.ITM_Reference = main.ITM_Reference)
LEFT JOIN (
    SELECT ID_ItemNominal, [ITM_Net]
    FROM TSub_ItmNominal 
    WHERE ID_ItemNominal ALIKE '4%' 
      AND YEAR([ITM_Date]) = [report_year] 
      AND MONTH([ITM_Date]) = [report_month]
) AS sub
   ON sub.ID_ItemNominal = main.ITM_Nominal

GROUP BY main.ITM_Nominal, 
         main.ItemNominal_Description

ORDER BY main.ITM_Nominal;

CodePudding user response：

You could join the original table with all rows and join your query to it like

SELECT t1.ID_ItemNominal, t1.ItemNominal_Description,t2.ITM_Net_Total
FROM TSub_ItmNominal AS t1 LEFT JOIN (SELECT ID_ItemNominal, ItemNominal_Description, Sum(Nz([ITM_Net],0)) AS ITM_Net_Total
FROM TSub_ItmNominal LEFT JOIN (T_Invoice RIGHT JOIN T_LineItems ON T_Invoice.ITM_Reference = T_LineItems.ITM_Reference) ON TSub_ItmNominal.ID_ItemNominal = T_LineItems.ITM_Nominal
WHERE (((Year([ITM_Date]))=[report_year] Or (Year([ITM_Date])) Is Null) AND ((Month([ITM_Date]))=[report_month]))
GROUP BY TSub_ItmNominal.ID_ItemNominal, TSub_ItmNominal.ItemNominal_Description
HAVING (((TSub_ItmNominal.ID_ItemNominal) Like "4*")) )  AS t2 ON t1.ID_ItemNominal = t2.ID_ItemNominal
WHERE ((([t1l].[ID_ItemNominal]) Like "4*"))
ORDER BY t1.ID_ItemNominal;