Crosstab report

[Guest]

The problem is, none of the fields are available for me me to select to
create the report ... it's blank. When I try to create a report using design
view, the pop-up box for parameter I set keeps coming up everytime I try to
move a field onto the report.
The Crosstab query has parameters set up for the Start Date and End Date, I
read that you have to set up column headings, does this mean that I have to
type 1/1/2007, 1/2/2007, .........until I reach 12/31/2007? I want the dates
to be column headings, the parameter would only be for 15 days, start:
01/01/2007 end:01/15/2007.

 

[Allen Browne]

With a crosstab, the column names come from the values in a field. Therefore
Access has to *run* the query in order to figure out what the field names
are, so you can design the report. To run the query, it has to ask for the
parameters.

Here's an alternative. Assuming your date/time field is named OrderDate,
open the crosstab in design view, and replace the OrderDate field that is
the Column Heading with this:
Day([OrderDate])
This will cause the column headings to be the numbers 1 to 15 instead of 15
dates. Consequently you don't have to redesign the crosstab every month to
get consistent field names.

Now in query design view open the Properties box. Viewing the properties of
the query (not of a field), enter the Column Headings property like this:
1,2,3,4,5, ...
This defines the column names. Benefits:
a) It ensures that there is a column for all values, even if there was not
field for that date. Consequently the report does not fail if there was no
data for a date.
b) Access now knows the names of the fields for the report *without* needing
to run the query. This makes designing the report much easier.

You wanted to use a parameter as well. Declare this parameter (Parameters on
Query menu.) If you always want just the first 15 days of the month, you can
do it with one parameter instead of two. Your criteria will be something
like this:
Between [Start Date] And ([Start Date] + 14)

For some other examples, see Duan Hookom's suggestions:
Dynamic Monthly Crosstab Reports
here:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm