If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Report Help Needed!
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. |
#2
|
|||
|
|||
Crosstab Report Help Needed!
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" wrote in message ... 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. |
#3
|
|||
|
|||
Crosstab Report Help Needed!
Thank you. I took off the extra () and it worked.
"Duane Hookom" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab query in report | Vincent DeLuca | Setting Up & Running Reports | 19 | July 13th, 2004 04:02 AM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |
Report Populated by a crosstab query | Michael Noblet | Setting Up & Running Reports | 3 | June 1st, 2004 07:11 PM |
Crosstab Report | news.microsoft.com | Setting Up & Running Reports | 3 | May 20th, 2004 05:26 PM |
Crosstab Report - Variable Columns | Ron Bajda | Setting Up & Running Reports | 1 | May 19th, 2004 06:17 PM |