I have a pivot table query returning the count of services done per model and region, per month for a twelve month array.
I have to run this query monthly and don't want to be rewriting this query for each new date parameter. So this is what I came up with:
PARAMETERS RecordDate DateTime;
TRANSFORM Count(mscqrySurveyServiceLaser.ServiceID) AS [Count of ServiceID]
SELECT ModelGroupNew.ModelGroup, mscqrySurveyServiceLaser.Region
FROM mscqrySurveyServiceLaser INNER JOIN ModelGroupNew ON mscqrySurveyServiceLaser.ModelNumber = ModelGroupNew.ModelNumber
WHERE (((mscqrySurveyServiceLaser.Region)<>"Unknown") AND ((mscqrySurveyServiceLaser.ServiceDate)>=[InstallationDate] And (mscqrySurveyServiceLaser.ServiceDate)>DateAdd("yy yy",-1,[RecordDate]) And (mscqrySurveyServiceLaser.ServiceDate)<=[RecordDate]) AND ((mscqrySurveyServiceLaser.ServiceType)<>"Factory Config.") AND ((mscqrySurveyServiceLaser.RecordDeleted)=False))
GROUP BY ModelGroupNew.ModelGroup, mscqrySurveyServiceLaser.Region
The problem is that if there were no services in any month within the parameters, then the month naturally does not appear. I'd like it to do so anyway, but can't think of clean solution. I somehow need to define that all months be shown, regardless of null values and without counting services outside of my date parameters.