Use the totals query and the modified crosstab below --
Bart_Total ---
SELECT Count([Resolution Rate].[Ref No]) AS Total_All,
Sum(IIf([Overall]="Beyond",1,0)) AS Beyond_1,
Sum(IIf([Overall]="Within",1,0)) AS Within_1
FROM [Resolution Rate];
TRANSFORM Count([Resolution Rate].[Ref No]) AS [CountOfRef No]
SELECT [Resolution Rate].Overall, Count([Resolution Rate].[Ref No]) AS
[Total Of Ref No],
IIf([Overall]="Within",[Within_1],[Beyond_1])/[Total_All]*100 AS [Percent]
FROM [Resolution Rate], Bart_Total
GROUP BY [Resolution Rate].Overall,
IIf([Overall]="Within",[Within_1],[Beyond_1])/[Total_All]*100
PIVOT [Resolution Rate].Status;
--
KARL DEWEY
Build a little - Test a little
Bart said:
here is the sample output:
Overall Total Closed Resolved
Beyond 13 12 1
Within 33 31 2
and I want to calculate using below formula
% Beyond = (12+1)/46
% Within = (31+2)/46
here is the sql...
TRANSFORM Count([Resolution Rate].[Ref No]) AS [CountOfRef No]
SELECT [Resolution Rate].Overall, Count([Resolution Rate].[Ref No]) AS
[Total Of Ref No]
FROM [Resolution Rate]
GROUP BY [Resolution Rate].Overall
PIVOT [Resolution Rate].Status;
:
Post sample data and the SQL of your crosstab query.
--
KARL DEWEY
Build a little - Test a little
:
Hi Karl!
Thanks. Sorry but I dont get it.
:
Build a query that gives you the full total (46 in this case) and join in the
crosstab query.
Then add anothe column heading for the percent presentation same like the
total. It will use the row total divided into the full total.
--
KARL DEWEY
Build a little - Test a little
:
I have an output from my crosstab query below
Overall Total Closed Resolved
Beyond 13 12 1
Within 33 31 2
But I want to calculate using below formula
% Beyond = (12+1)/46
% Within = (31+2)/46
should I do the calculation on the report? Or on the query? How Should I do
this?
