Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Question 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
    PIVOT Format([ServiceDate],"_yyyymm_");

    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.

    Any ideas out there?

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    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.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    It would show a column for each month that would contain 0 or some other number...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •