Crosstab Report Help Needed!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a crosstab query. I have specified my column headings, but when there is no data for one of the columns, the calculation on my report does not work. Below is the SQL for the crosstab query and also the calculated fields on the report.

Crosstab Query:
TRANSFORM Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround
SELECT qryTurnAnalysis.ClientAdministrator, Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround1
FROM qryTurnAnalysis
GROUP BY qryTurnAnalysis.ClientAdministrator
PIVOT Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48 hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6]","1 wk",[Turnaround] Like "[7-8-9]","7-9",[Turnaround] Like "10","10",[Turnaround] Like "11","11",True,">2 wks") In ("24 hrs","48 hrs","72 hrs","1 wk","7-9","10","11",">2 wks");

Report Calcuation
=Nz(([7-9])+Nz([10])+Nz([11]))

There is no data for the column field [7-9],but there is data for [10] and [11]. It will not add the columns together.

Any help will be greatly appreciated.
 
I find it hard to believe the number of times I see Nz() without providing a
second argument.
=Nz([7-9],0)+Nz([10],0)+Nz([11],0)
In addition, your ()s were off a bit.

--
Duane Hookom
MS Access MVP


Karen said:
I have a report based on a crosstab query. I have specified my column
Click to expand...
headings, but when there is no data for one of the columns, the calculation
on my report does not work. Below is the SQL for the crosstab query and
also the calculated fields on the report.
Crosstab Query:
TRANSFORM Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround
SELECT qryTurnAnalysis.ClientAdministrator,
Click to expand...
Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround1
FROM qryTurnAnalysis
GROUP BY qryTurnAnalysis.ClientAdministrator
PIVOT Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like
Click to expand...
"[3]","48 hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6]","1
wk",[Turnaround] Like "[7-8-9]","7-9",[Turnaround] Like
"10","10",[Turnaround] Like "11","11",True,">2 wks") In ("24 hrs","48
hrs","72 hrs","1 wk","7-9","10","11",">2 wks");
Report Calcuation
=Nz(([7-9])+Nz([10])+Nz([11]))

There is no data for the column field [7-9],but there is data for [10] and
Click to expand...
[11]. It will not add the columns together.
 
Back
Top