Show query results by quarter even if blank



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
GROUP BY (Format([Date],"\Qq yyyy")),
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?




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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question