crosstab query calculation

[rutica]

Can I write an expression in a crosstab query to do a calculation for my Crosstab Value?

Please see frmEscalationReports in the attached database. Choosing values from the 2 drop down boxes and clicking on the button opens the crosstab query. Currently my Crosstab Value just gives a count.

Instead of a Count, if the Column Headers are "Maint Overrun","Maint Underrun", "Dev Overrun" or "Dev Underrun", I would like to do a math calculation to find the variance.

I tried:

Expr1:
IIf([CodeGrouping] Is Null,Count([tblEscalationReportsDetail]![Criteria Code]),
IIf([CodeGrouping] In ("Maint Overrun","Maint Underrun"),(([YTDCostActualMaint]-[YTDCostPlannedMaint])/[YTDCostPlannedMaint]),
IIf([CodeGrouping] In ("Dev Overrun","Dev Underrun"),(([YTDCostActualDev]-[YTDCostPlannedDev])/[YTDCostPlannedDev]))))

What I'm saying is, go to tblEscalationReportsDetail and look at the column called CodeGrouping. If that is null, then use the Count of Criteria Code in that same table for the value.

If CodeGrouping equals "Maint Overrun" or "Maint Underrun", then do a calculation using YTDCostActualMaint and YTDCostPlannedMaint from the Project Status Survey Budget table.

If CodeGrouping equals "Dev Overrun" or "Dev Underrun", then do a calculation using YTDCostActualDev and YTDCostPlannedDev from the Project Status Survey Budget table.


But I am getting a message 'You tried to execute a query that does not include the specified expression 'IIf([CodeGrouping] Is Null,.....' as part of an aggregate function'

Is my logic correct or can't this be done?

 

[rutica]

Just wondering if anyone had any suggestions.

Thanks!

 

[rutica]

some more information. I'm almost there...

I'm trying to write an IIF statement for the Value of my Crosstab query.

If you open the attached database, chose a Report and a Date and click the button, you'll see the query (qrySummary) shows 2 columns of percentages. Then there are '1's in the grid of the crosstab query.

What i would like is:

for the 4 columns titled: Dev Overrun, Dev Underrun, Maint Overrun, Maint Underrun, replace the '1's with the percentages in the grid.

It seems like I can do one or the other, but not both.
--I can either have '1's in the grid for everything (as shown in the qrySummary) or

--I can have percentages for the Dev Overrun, Dev Underrun, Maint Overrun, Maint Underrun columns, but not have '1's for the other columns (as shown in qrySummary2).

Can I have both the '1's and the percentages?

I have spent about 4 hours on this so far, and no luck!

 

[rutica]

Anyone have any suggestions for me?

thanks,

 

[rutica]

I figured it out! Yeah.
If anyone is interested, my Value SQL for my Crosstab query is:

Expr2: IIf(First([CodeGrouping]) Is Null,Count(tblEscalationReportsDetail![Criteria Code]),IIf(First([CodeGrouping]) In ("Dev Underrun","Dev Overrun"),First(FormatPercent([dev cost Variance],1)),IIf(First([codeGrouping]) In ("Maint Underrun","Maint Overrun"),First(FormatPercent([Maint Cost Variance],1)),Null)))