Creating Crosstab Queries with Months that have no data.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's the situation:

I created a Query with Sums and Counts for all the records I wanted and I
wanted them to be divided by their corresponding months. I then ran a
Crosstab Query for that Query to make it appear in a table-like fashion. I'm
using that Crosstab Query to create a Report. The problem is is that I need
to show all 12 months regardless or whether there are records for that month.
For example: there would be no records for September since it hasn't happened
yet so the Report should say Zero (0) under September. I want to know if
there is a way to get the Crosstab Query or the query that I started with to
return Zero values for the remaining months of the year since there are no
records for those months. So in turn I can just take the Crosstab Query and
put it into a Report. Any help would be greatly appreciated.
 
To get a column for all 12 months:
1. Open your crosstab query in design view.

2. Open the Properties box (View menu).

3. Set the query's Column Headings property to each possible value, e.g.:
1,2,3,4,5,6,7,8,9,10,11,12

The results will be a Null at the intersection point where there is no data,
and that's probably appropriate. If you want a zero instead, change to SQL
View (View menu), and wrap Nz() around the expression in the first line. JET
could then misunderstand the data type, so wrap that in CCur(), DDbl(),
CLng() or whatever.

The first line of the query will end up with something like this:
TRANSFORM CCur(Nz(Sum([MyField]),0)) AS SumOfMyField
 
Back
Top