Crosstab query, make every category show

[TheSmileyCoder]

I have a crosstab query where I have Main item as the rows, and Category as the columns, and count(Category) as the value.

Basicly im counting how many observations of each category that is for each Main Item. My problem is that there is no Category A observations, my crosstab will not show that column.

How can I make the each of the category's show up, even if the count is 0?

Thank you

[MikeTheBike]

@TheSmileyOne
Hi

If the 'catergoies' filed has a fixed number of categories (and names), then you can specify what columns are included in the cross tab by entering then in the query's 'column heading' of its property dialogue (this will also specify the order of the columns). These columns will be included even when no data is returned.

The line below is an example of the same thing in SQL view (this order the months in the correct order as sorting the list will not!).

PIVOT Format([absdate],"mmm") In ("Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct");

Note; Our finacial year is from November to October.

I believe there in som info in access help on this topic, if you know where to look(press F1 with the Column Headings field of the query property dialogue selected).

HTH


MTB