How do I include zero-sale months in a Crosstab query?

[Frank Martin]

I have made a crosstab query to show the sales of all our products per
month.

The left hand column is Products, and the Row headings are Months.

This is based on a summation query where the "InvDate" is expressed as
"yyyymm" and so the crosstab now shows all sales per month going back to
July 1999.

The problem arises when I select just one product for viewing, and this is
that all the non-sale month columns have disappeared and so the crosstab
shows only the months with sales.

Since I want to use this crosstab for a chart I need the non-sale months
included to discern trends.

Please help, Frank

 

[Duane Hookom]

You would need to add all the potential months to the Column Headings
property of the crosstab query.

 

[Frank Martin]

Thank you.

Do you mean I will have to add all future months manually into the crosstab?

Regards, Frank

 

[Duane Hookom]

I don't. Consider creating crosstabs with relative months. Use a column
heading expression like:
ColHead: "M" & DateDiff("m",[YourDateField],Date())
Decide how many months you want to ALWAYS display and set the column
headings property to:
Column Headings: "M0","M1","M2",..."M11"
M0 will show values for the current month and M11 will be 11 months earlier.