Basically I have this query below, that gives me the number of sales per week, but it skips the weeks where there were no sales for a specific item. I would like it also to display those as 0 values.
And by the way, is it possible to merge the two queries below? I tried but couldn't get the joins to work.
SELECT tblWeek.WeekNum, [Query - Sales].ModelName, Count([Query - Sales].OutDate) AS CountOfOutDate
FROM tblWeek LEFT JOIN [Query - Sales] ON tblWeek.WeekNum = [Query - Sales].WeekNumber
GROUP BY tblWeek.WeekNum, [Query - Sales].ModelName
HAVING ((([Query - Sales].ModelName) Is Not Null))
ORDER BY [Query - Sales].ModelName;
SELECT DDL_Items.ModelName, DDL_Items.OutDate, DDL_Models.Type, DatePart("ww",[OutDate],2) AS WeekNumber
FROM DDL_Models INNER JOIN DDL_Items ON DDL_Models.ModelName = DDL_Items.ModelName;
The first thing you want to do is create a Cartesian Join between your model name and the weeks. Something like:
SELECT DISTINCT [Query - Sales].ModelName, tblWeek.WeekNum FROM [Query - Sales] LEFT JOIN tblWeek
This will create one record for every week for every model name.
Then, you want to join the actual data to this query:
SELECT qryA.*, Count([Query - Sales].OutDate) AS CountOfOutDate FROM ( SELECT DISTINCT [Query - Sales].ModelName, tblWeek.WeekNum FROM [Query - Sales] LEFT JOIN tblWeek ) as qryA LEFT JOIN [Query - Sales] ON tblWeek.WeekNum = qryA.WeekNumber GROUP BY tblWeek.WeekNum, qryA.ModelName HAVING qryA.ModelName Is Not Null ORDER BY qryA.ModelName;