How do I create a Totals column for this crosstab query

[str8trini]

I have been able to successfully create and apply a monthly dynamic crosstab query using the query below and apply the output to a report.

TRANSFORM First(IIf([SortCOL]=1,[Group],IIf([SortCOL]=2,IIf(IsNull([GCL]),Null,Round([GCL]/1000000,2) & " M"),IIf([SortCOL]=3,Round([GCL_Target]/1000000,2) & " M",IIf([SortCOL]=4,Format([GCL_PctOfTarget],"Fixed") & "%",IIf([SortCOL]=5,[GCL_BW_Target],IIf([SortCOL]=6,[GCL_Rank],""))))))) AS RowTitle
SELECT qryScorecard.FC_ID, qryScorecard.TeamID, qryScorecard.EmpType_ID, qryScorecard.PerfTypeID, tblRptLabels.Title
FROM qryScorecard INNER JOIN tblRptLabels ON qryScorecard.EmpType = tblRptLabels.EmpType
WHERE (((tblRptLabels.RptType)="GCL") AND ((qryScorecard.FC_ID)=[Forms]![frmScorecardRpt]![cboEmployee])) OR (((tblRptLabels.RptType)="GCL") AND ((qryScorecard.TeamID)=[Forms]![frmScorecardRpt]![cboTeamLead]))
GROUP BY qryScorecard.FC_ID, qryScorecard.TeamID, qryScorecard.EmpType_ID, qryScorecard.PerfTypeID, tblRptLabels.Title, tblRptLabels.SortCOL
ORDER BY tblRptLabels.SortCOL
PIVOT "Mth" & DateDiff("m",[PerfMth],[Forms]![frmScorecardRpt]![txtYearEnd]) In ("Mth11","Mth10","Mth9","Mth8","Mth7","Mth6","Mth5 ","Mth4","Mth3","Mth2","Mth1","Mth0");

This is the picture of my results:

FC_ID	TeamID	EmpType_ID	PerfTypeID	Title	Mth11	Mth10	Mth9	Mth8	Mth7	Mth6	Mth5	Mth4	Mth3	Mth2	Mth1	Mth0
FCCFO01	4	1	1	GCL Actual		0.17 M
FCCFO01	4	1	2	BC GCL Actual	5.81 M
FCCFO01	4	1	1	GCL Target		0.13 M
FCCFO01	4	1	2	BC GCL Target	4.37 M
FCCFO01	4	1	1	GCL Pct of Target		128.00%
FCCFO01	4	1	2	BC GCL Pct of Target	133.01%
FCCFO01	4	1	1	GCL B/W Than Target		Worse
FCCFO01	4	1	2	BC GCL B/W Than Target	Worse
FCCFO01	4	1	1	GCL Rank		22

After everything was totally completed I was asked to add a Total/calculated column with YTD totals but I can't figure out how to modify my query to do so. As a last resort I created a YTD crosstab query and report which could work but now I can't use it as a subreport in my original report. Any suggestion and/or help would be very much appreciated.

[str8trini]

As I was sitting here contemplating and googling my problem further I had a burst of inspiration based on one of my google results. I should be modifying my select statement instead of my transform statement (I find crosstab queries very confusing to understand). So I am modifying one field at a time and it appears to be working. I haven't yet figured out how to format the YTD columns as was done in the monthly columns. My next problem would be placement of the YTD column as the last column on the report, but this is progress!

SELECT qryScorecard.FC_ID, qryScorecard.TeamID, qryScorecard.EmpType_ID, qryScorecard.PerfTypeID, tblRptLabels.Title, IIf([SortCol]=2,Round(Sum([GCL])/1000000,2), IIf([SortCol]=3,Round(Sum([GCL_Target])/1000000,2))) as YTD

FC_ID	TeamID	EmpType_ID	PerfTypeID	Title	YTD	Mth11	Mth10	Mth9	Mth8	Mth7	Mth6	Mth5	Mth4	Mth3	Mth2	Mth1	Mth0
FCTDC13	1	1	1	GCL Actual	0.46	0.35 M	0.11 M
FCTDC13	1	1	1	GCL Target	0.38	0.23 M	0.15 M
FCTDC13	1	1	1	GCL Pct of Target		153.58%	71.36%
FCTDC13	1	1	1	GCL B/W Than Target		Worse	Better
FCTDC13	1	1	1	GCL Rank		17	4

[CJ_London]

where it appears in the query is irrelevant - in a form or report you can put the column wherever you like

[str8trini]

where it appears in the query is irrelevant - in a form or report you can put the column wherever you like

Okay, Thanks for the response. I should have known that. I think I let crosstab queries intimidate me because they are so quirky

New problem with one of my If statements. Can you take a look and tell me what I'm doing wrong?

I am getting #Error in my result for GCL B/W Than Target YTD column

This is my Select statement, and the bold text is what results in the error:
SELECT qryScorecard.FC_ID, qryScorecard.TeamID, qryScorecard.EmpType_ID, qryScorecard.PerfTypeID, tblRptLabels.Title, IIf([SortCol]=2,Round(Sum([GCL])/1000000,2), IIf([SortCol]=3,Round(Sum([GCL_Target])/1000000,2),IIf([SortCol]=4,Format((Sum([GCL])/Sum([GCL_Target])*100),"Fixed"),IIf([SortCol]=5,IIf((Sum([GCL])/Sum([GCL_Target])*100)=100,"Met",IIf((Sum([GCL])/Sum([GCL_Target])*100)>100,"Worse","Better")))))) as YTD

[CJ_London]

you can divide sums like that - you also have unnecessary multiplication - try

IIf((avg([GCL]/[GCL_Target])=1,"met"...

although I have a feeling that won't give you the result you want - so more likely you will need another query using this query as its source where you can do your division

[str8trini]

you can divide sums like that - you also have unnecessary multiplication - try

IIf((avg([GCL]/[GCL_Target])=1,"met"...

although I have a feeling that won't give you the result you want - so more likely you will need another query using this query as its source where you can do your division

I played around with the formulas and this is what I came up with which seem to have worked. I'll mark it resolved. Thanks!

SELECT qryScorecard.FC_ID, qryScorecard.TeamID, qryScorecard.EmpType_ID, qryScorecard.PerfTypeID, tblRptLabels.Title, tblRptLabels.SortCol, IIf([SortCol]=2,(Round(Sum([GCL])/1000000,2) & " M"), IIf([SortCol]=3,(Round(Sum([GCL_Target])/1000000,2)& " M"),IIf([SortCol]=4,(Format((Sum([GCL])/Sum([GCL_Target])),"Percent")),IIf([SortCol]=5,IIf((Sum([GCL])/Sum([GCL_Target]))=1,"Met",IIf((Sum([GCL])/Sum([GCL_Target]))>1,"Worse","Better")))))) AS YTD