Crosstab results in a report

[Siraj Nawas]

Hi

I created a table to track a certain document type in construction management.
The fields are an Unique No, Description, Descipline ( Civil, Architectural, Mechanical, Electrical, etc ), Responible, Status ( A, B, C, D, etc ), etc.
I created a crosstab query to get this result :

By Descipline

Total Submitted Pending Total with Status A Total with Status B , C, D etc.

Based on the results i created a report also.

Now the problem is the report is giving error if even one of the result is missing in the cross tab query results..

Please help me.

[Stewart Ross]

Because the columns in crosstab queries may vary in line with the range of pivot values within the current data they are not altogether the best source for a report. A report is not intended to handle fields that were there when the report was created but which are no longer present in the underlying data at runtime.

If the full set of the headings can be defined in advance, even if the actual headings which are in the current run cannot be known, you can resolve the problem within your crosstab query itself by defining the column headings as a list.

In the query editor right-click on the field you have defined as the column heading (which may be the Status field, although you do not say so above). In the Column Headings property enter the complete list of values which your column headings can take, separated by commas. If these are text values enclose each entry in double-quotes, like this:

"First Value", "Second Value", "Third Value", "Fourth Value"

Save the query when you finish entering the list.

You will find when you run the query that all of the columns will be present, even if there are no entries matching that column in the current data (in which case the computed total will be returned as a null). This in turn will ensure that your report does not fail as a result of a missing column heading.

-Stewart

[Siraj Nawas]

Thank you so much Mr. Stewart.
It is working fine and this is exactly i want. ofcourse the Status column which has the values A, B , C and D. My crosstab column headings are infact these status only. Now I am experiencing a problem as i dont know what to do with the the values which are not included in these status , I just want show the empty calues to be counted and shwon as "Pending "
Please help me.

[Siraj Nawas]

Thanks Stewart. Indeed it was a great help.
I solved the problem, but dont know whwther it is the correct method. I changed the column headings as ""A","B","C","D","<>" "

Wish I would learn more from you.

Siraj Nawas