By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Crosstab query, make every category show

TheSmileyCoder
Expert Mod 100+
P: 2,321
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
Apr 23 '10 #1

✓ answered by 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!).

Expand|Select|Wrap|Line Numbers
  1. 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

Share this Question
Share on Google+
1 Reply


Expert 100+
P: 636
@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!).

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 23 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.