Crosstab query to report

[Geraldo]

How can i make a report to a crosstab query when the crosstab query can have more or less columns depending of data included.
If i make a report today, tomorrow it could have one more field or one less field, and ocurres an error.

Any Help Urgent

Geraldo

 

[catkins]

Good question Geraldo...I know of no solution other than the painfull truth of the following:

Your crosstab creates a table. That table appends to a new table with genaric field names and your report is based on the genaric field names. You'd have to change the labels in your report manually. If you find any other way around this pleae let me know.

-Chris catkins1@mindspring.com

 

[catkins]

Thinking about my reply again, this would not work given that the number of fields would always change...unless you knew that at some point there would be a finite number of fields.

Good luck!

 

[JanetFan95758]

By default, Access presents the summarized columns in alphabetical order from left to right. Missing values are returned as NULL. If no values at all exist, then the column is not created.

You can force Access to create columns in an order other than alphabetical and force the creation of columns even if there is no data for that column value, by using the optional IN clause with the PIVOT clause, i.e.:

TRANSFORM Avg (Employees.Salary) AS AvgOfSalary
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
PIVOT Employees.Country In ("UK","Germany","France","USA&quot

 

[colinb]

I have devised a workaround that I regularly use:
Firstly, forget about Crosstabs.

Say you want to return Columns A, B and C where B has no entries but you want the report to display B=0.

In your base query 'force' the desired columns to return a value like so:

B:IIf([field] In ("b1","b2","b3&quot,1,0) etc

Then in your report display Sum(A), Sum(B) etc. and magically B is displayed as 0.

This method actually runs quicker than the equivalent Crosstab!