crosstab query with parameters

[Lindsay]

I am creating a crosstab query to do a monthly report, so I need to be
able to enter the new date restrictions everytime that I run it. Right
now my row headings are the departments, and my columns are the
purchasing agents. I am counting the amount of orders that each
purchasing agent processed per department, per month. In which field
should I put my parameters? The query is counting by PO number. Any
help would be appreciated.

 

[Guest]

Whoops! Hit the send button too soon. Here's what the column headings would
look like:
'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

 

[Guest]

Hi Lindsay,

First create a query that returns the records that you want. This query
should have any parameters needed by the crosstab. Make sure that you go up
to Query, Parameters and define the parameter datatype. I'm assuming that
you have a date field and probably need two parameters like [Start Date] and
[End Date] in a between statement like:
Between [Start Date] and [End Date]

In that case both parameters need to be entered and set to Date/Time.

Next create the crosstab based on this query instead of the tables directly.
In a nutshell a query based on a query.

For a report you need to know the column headings or it will bomb out. There
are two ways of doing this. The first is to pre-populate the Column Headings
property of the crosstab query. That way the text boxes on the reports will
always know where to find the data. It has other benefits such as putting the
columns in a order that you like (such as months in chronological order
instead of April, August, etc.). It will also hold open a field such as for a
month that had no data or exclude a month if you don't want to see June for
example. If you use the Month function on a date field, you could use column
headings like below: