Crosstab

[Guest]

Hello

I am trying to create a crosstab report that shows location (rows) and type
of incident (columns).
But i want to make sure it asks for a date range. when i add date of
incident to the query and type in the criteria between[start date]and[end
date]. start date and end date are set as parameters in the query.

Now when i run the query alone, it works fine, however when i try to attach
it to display in a report, it asks me twice to enter the start and end dates
before i will run anything, and also every couple of seconds the start date
box will appear randomly when i am in the design view of the report, even
when i press cancel it pops up again a few seconds later.

Can any one offer any advice??

 

[Allen Browne]

Yes, that makes sense. In report design, Access needs to know what the field
names are. But with a crosstab query, it doesn't know what the field names
are until you run the query.

For example, if you chose a very narrow set of dates (e.g. just one day),
some of the types of incident may not appear, so there are not columns for
those types, so the fields do not appear. This is a serious problem for the
report based on the query if columns just disappear like that.

To avoid the problem, open the crosstab query in design view, and open the
Properties box. Type in all the possible values for type of incident beside
the Column Headings property. Access will now show these columns whether
they occur in the date range or not. The fields will be present for the
report so it will not fail. And, Access knows the column names (from the
property) without having the run the query, which solves your design
problem.

The biggest practical issue here is to remember to add more items to the
property in the query whenever you add them to the lookup table.

 

[Guest]

1. Create a query that grabs all the needed data with the parameters. Make
sure to go up to Query, Parameters and define the parameters as Date/Time.

2. Create the crosstab based on the first query. In the crosstab lock in the
columns with the Column Headings property of the crosstab. While design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

This should match the expected data. The list should also match the list of
fields in the report.

3. If the date range isn't needed in the report, make sure that its not in
the report sorting and grouping options.