I have created a report based on a crosstab query.

The crosstab query shows me the count of activity (counts the dates posted for a month) for 2 categories (Cat1, Cat2), and totals these counts by month (Total Activity (Count(*)).

That is, I have

FIELD:........Year............Month.......Cat1.... ...Ca t2.......Total Activity
TOTAL:.......Group By......Grp By.......Count.....Count......Expression
CROSSTAB:..Row Head.....Row Head..Value......Value......Row Heading


However, when there is no activity for a month, the month does not show--but the boss wants a line generated anyway.

Is there a way to get Month to show even when no activity occurs?

I'm thinking I could put an IIf statement in the criteria line for Month...but Access prompts me for a "Total Activity" value the way I have it now...

iif([Total Activity] is null,[Month],[Month])

any ideas appreciated,
sjl

I can't figure out a way to do this when month is the ROW, but if you switch the layout around so month is the COLUMN, then there is a way to force column headers.

1. Make Month a column heading
2. Still in design view right-click Month and select properties
3. On the Column Headings line, type in the values you want, separated by quotes and commas e.g. "Jan","Feb",Mar", etc.

Now, even if you have no data for Feb, you will still get a column for it.

If anyone knows a way to do this for a ROW, I'd be thrilled to be proved wrong.

You can do it in rows... But you have to have dummy data either in a union or in your source it self. No other way that I know of (like the header part)

Thanks Big Pat and Mailman

I will see what the boss says about transposing the data lay-out. ;)

Appreciate your help/expertise.
sjl