Report Stopped Working 1

[itemus]

I have a report that is generated from a Crosstab Query. One of the fields (Category) in my table can contain values of: A,B,C,D,E or blank.

My report Looks something like this:

Contractor # of # with #of #of #of #of #of
Technician Inspects 0 faults A B C D E
------------------------------------------------------------------------------------
ABC
Tech1 21 19 1 1
Tech2 15 15
Tech3 6 3 2 1
Tech4 10 8 1 1
-----------------------------------------------------------------------------------
Total 52 45 2 1 2 1 1
% of Total 87% 3.9% 1.9% 3.9% 1.9% 1.9%


Because I am trying to make the report standard in appearance the heading and the detail records with summaries.
This report will print IF there is at least a record of A,B,C,D,or E in the Category. Otherwise the Jet database engine does not recognize "Report name" as a valid field name or expression. So if all the Techs did not commit a fault in a certain Category then the reports bombs.
How can I get around this?

Thanks.

John

 

[JoeMiller]

In your crosstab query you can hard code the column names, click on properties then click in the grey area up top where the table shows. Now change the column headings property to A,B,C,D,E and those columns will always appear whether there is data or not.

HTH Joe Miller
joe.miller@flotech.net

 

[itemus]

Thanks Joe, but this doen't solved the problem. Here is what my Crosstab query looks like:

Field Table Total Crosstab Criteria
Contractor Inspect Group by Row Heading
Technician Inspect Group by Row Headiing
Region Inspect Group by Row Heading
Fault Cat Inspect Group by Column Heading
No Faults* Inspect Count Value
Total Inspections Inspect Count Row Heading
Technician Inspect Where Is Not Null

Here is the SQL view.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count(Inspect.[Total Faults]) AS [No Faults]
SELECT Inspect.Contractor, Inspect.Technician, Inspect.Region, Count(Inspect.[Total Faults]) AS [Total Inspections]
FROM Inspect
WHERE (((Inspect.Technician) Is Not Null) AND ((Inspect.[Completion Date]) Between [Start Date] And [End Date]) AND ((Inspect.Department)="Service&quot)
GROUP BY Inspect.Contractor, Inspect.Technician, Inspect.Region
PIVOT Inspect.[Fault Cat];

When I followed your advice in varies configurations in my column headings I eitherwind up missing the column Heading "Number of No Faults" or worse still, it starts to ask for paramters for some of the other column headings. Is there something I am not getting here?

Thanks.

John

 

[JoeMiller]

I don't see anything wrong with your crosstab from here, maybe something deeper is at fault. What column(s) does it ask for parameters? You said various configurations, my solution was to put in ALL variables, not just some.. did you do that? Any chance you could send me a copy of this DB so I could take a quick look? Joe Miller
joe.miller@flotech.net