Crosstab Query Help

[fredk]

I have a crosstab query that has:

Row Heading:
Sales Rep
Goal
Year

Column Heading:
Month (january, february, etc)

Data is the number of groups they sold.

I am trying to insert 0's in the report where there are no groups sold. I tried using NZ and then in the report using and if statement:
=iif(isnull([feb],0,[feb])

Not working -

Any help would be greatly appreciated!!!

Fred

 

[MichaelRed]

this is essientally 'forcing' the col headings (jan, feb, ... dec) to exist? If so, a way to do it is:

generate a small recordset (refer to it as "tblReqHeads" for this explination) which includes the desired headings in a col (call it ReqHead for this explination).

left Join this col [ReqHead0 to the 'nonth' col of your current source for thre crosstab query.

use the [tblReqHeads].[ReqHead] as the col heading selection of your crosstab query.

otherwise I didn't understand the issue correctly.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[fredk]

that is excacly correct and that worked. thank you very much for your help!!!

Fred

 

[CosmoKramer]

Fred,

Another way to force column headings to exist is to define them in the Pivot statement of your crosstab query. Something like this:

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

 

[fredk]

Thanks Kosmo... I was actually looking at one of your recent posts regarding a crosstab query where you use this. Thanks much for the help!!!

Fred