Unanswered: Pivot tables, date parameters and null values
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.
Here's a SQL statement (that works with the free sample Access database from my site at www.bullschmidt.com/access) that uses calculated fields containing the IIf() function and groups by customer showing the invoice totals with a calculated field for Sunday and a calculated field for Monday (and the rest of the days of the week could be added the same way too):
SELECT InvCustID, Sum(IIf(Weekday(InvDt)=1,InvTotal,0)) AS InvTotalSunday, Sum(IIf(Weekday(InvDt)=2,InvTotal,0)) AS InvTotalMonday FROM tblInv GROUP BY InvCustID;
But the same concept could be applied using Month() instead of Weekday() to show months and a pivot table wouldn't even be needed.
Thank you. I'm not yet familiar with all the available functions, so your info was very interesting. Still, I don't see that it's going to help me return a month that the data doesn't actually contain.