Show query results by quarter even if blank


J

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?
 
Ad

Advertisements

G

Guest

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

Top