Show query results by quarter even if blank

[john.kedzierski]

I am trying to create a report in MS access 2003 that contains a chart
that shows the frequency of an event by quarter. I have successfully
created a crosstab query that does this, however in quarters where the
event did not occur my report simply skips the quarter. I went it to
list the quarter, for example 'Q1 2003" and just show zero in that
quarter. Here is the query I am using right now:

PARAMETERS [Forms]![Stuff_form]![Vendor] Text ( 255 );
TRANSFORM Count([Stuff].[item]) AS [CountOfitem]
SELECT (Format([Date],"\Qq yyyy")) AS Expr1
FROM [Stuff]
WHERE ((([Stuff].[item]) Is Not Null) AND ((Year([date]))=2003 Or
(Year([date]))=2004 Or (Year([date]))=2005 Or (Year([date]))=2006) AND
(([Stuff].[Vendor])=[Forms]![Stuff_form]![vendor]))
GROUP BY (Format([Date],"\Qq yyyy")),
(Year(date)*4+DatePart("q",date)-1)
ORDER BY (Year(date)*4+DatePart("q",date)-1)
PIVOT [Stuff].[item];

So in my specific case I want to show by quarter how many times
stuff.item was sold, based on the parameter vendor in my form.
However, if none where sold in a given quarter I still want to see a
row returned with the quarter and 0 in it that way when the chart is
drawn the quarters all appear consecutively across the bottom without
any being skipped over.

Any ideas?

 

[Ken Sheridan]

Add an IN clause listing all the quarters in the date range:

PIVOT [Stuff].[item] IN("Q1 2003","Q2 2003",<and so on>"Q3 2006","Q4 2006");

Ken Sheridan
Stafford, England