CROSSTABS with dynamic columns?

[Savvoulidis Iordanis]

I need to display some numbers from a table that are inserted on different
days in a month, in a crosstab report.
The report, has also group sections and sums the numbers in every date
column in the report.

Now, when it comes to February or any month that has 30 days, the crosstab
report gives an error, that it can't find a specific date as a column
object.

Also, when I create the crosstab query, I tried 2 different ways. One (and
also the preferable) was to display only a computed column with the
expression "=day([date_field])" . This gave me only the day number but of
course I have the same problem when I run the report for February.

The other way is to leave the date column intact, but then, the PIVOT
statement, returns all the date values in the specified month. The problem
with this, is that they are fixed (e.g. 1/2/2005, 2/2/2005, 3/2/2005,...),
which means that the column names and controlsource values are fixed too. If
the year changes, they will not be found in the report.

How can I overcome this? (Don't forget that I definately need the sums on
every group)
I believe this is a very common type of report, so if anyone has found any
solution I'd be more than pleased.

TIA
Savvoulidis Iordanis
Greece

 

[Guest]

You're allowed to write your sql's in VBA as long as you can write a function
that will generate your column names from the report selections it can be
done. Much simpler speaking in your query (right click) properties you can
force your column names. I have many monthly reports where all i use is 1-31
as my column headings in a crosstab query and then replace the column label
in the report with its complete date with some computation from a form I used
for report selections. In the query I use those dates in a between #date1#
and #date2# so I know what i'm getting.

HTH
Martin J