I have a query where I am trying to extract values from a table for every date in a given time period. If no entries were maded for a given date, there is no entry in the RIGHT Table. I want to include those nulls as well (as zeroes)
to do that, I have attempted to create an Outer Join where the data table is Left Joined with a table of all possible dates . To my understanding, if there is no corresponding record in the Right Table for a date in the Left Table, the Left Outer Join should return a null for that date.
Instead, it is acting as a regular Select query. I am only returning values for data that exists in the Right table. Below is my SQL. Can somebody please tell me what I am doing wrong? Thanks.
SELECT KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")" AS Defect INTO KO_QN_EFR_01C_AllQNS_DetailSum_Monthly_Table
FROM PossibleCreateDates LEFT JOIN KO_QN_Data ON PossibleCreateDates.PossCreateDates = KO_QN_Data.[Created on]
WHERE (((KO_QN_Data.[Short text for code])="Kimball Office Furniture") AND ((GetNOFGSA([Product Hierarchy]))="Not NOF GSA") AND ((KO_QN_Data.Field20)="White Residue"))
GROUP BY KO_QN_Data.[Created on], (KO_QN_Data.Field20) & " (" & [KO_QN_Data.Code group] & ")", KO_QN_Data.Field20;