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

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

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
 
You would need to add all the potential months to the Column Headings
property of the crosstab query.
 
Thank you.

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

Regards, Frank
 
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.
 

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

Similar Threads

Including zero-sales months. 3
Crosstab queries - populating blanks with zeros 3
Totalling Rows and Columns in a Crosstab Query 1
I need a xtab query to show zero months. 1
Crosstab query Date Span 7
How to link Crosstab Query to other query problem... 7
How can you compress / group by on a crosstab query when column names are unknown 2
force zero into null in value field of a crosstab query 9

Back
Top