Crosstab Report

[rberry]

I have set up a crosstab report which is tabulating ethnicity vs ward. I
don't have any problems setting up the crosstab query or report. This then
gives me a nice crosstabulation of all ehtnic codes vs wards in its entirety.

However if I try to add a criteria which may filter the data-set, I hit a
problem as the query may exclude specific wards and when I come to run the
report I get an error 3070 saying that the text field specified in the report
is not found in the underlying query.

I can fix it by deleting the offending text fields but I want a dynamic
solution which responds to changing events. Any ideas.

 

[Duane Hookom]

You can enter each possible column heading value into the Column Headings
property of your crosstab query.

 

[rberry]

Duanne
Give us a bit more. I don't see quite how that will solve the problem.

Regards Richard

 

[John Spencer]

PERHAPS the following will make it clearer.

In the crosstab query you can specify the field name(s) using an In
clause in the PIVOT statement. When you do so, the columns will appear
even if there is no data for the column returned.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY
those crosstab columns will be visible. If you mistype a value, you
will get a column with that name and no data (all nulls) in that column.

 

[rberry]

John/Duanne
Many thanks, I now understand what you are saying.