Missing Months in Cross tab Query

  • Thread starter Thread starter Angel_G
  • Start date Start date
A

Angel_G

I have a crosstab query that gives me the results of complaints per month.
It looks like this:

TRANSFORM Count(Charts.[QA Defect Code ID]) AS [CountOfQA Defect Code ID]
SELECT (Format([Date Received],"mmm")) AS Expr1
FROM Charts
GROUP BY (Year([Date Received])*12+Month([Date Received])-1), (Format([Date
Received],"mmm"))
PIVOT Charts.[Defect Code];

However there are times where we do not have any complaints at all per month
but I still want to display the month with a zero value so that we know it
just did not get ignored. Can anyone help me modify my script so that the
select part of my code inserts the results into its pertaining month?
I have tried but with out any luck:

TRANSFORM Count(Charts.[QA Defect Code ID]) AS [CountOfQA Defect Code ID]
SELECT (Format([Date Received],"mmm")) <<<< How would you insert the
results into the pertaining month here?>>>>
FROM Charts
GROUP BY (Year([Date Received])*12+Month([Date Received])-1), (Format([Date
Received],"mmm"))
PIVOT Charts.[Defect Code];

Thank you for taking the time to help!
 
You can't just modify your existing SQL: SQL cannot report on what's not
there.

You'll need to create a table that contains all of the months of interest,
and do a left join with your data to ensure that there's at least one row
for each month.
 
Back
Top