IIF in Crosstab Expression

[kagard]

Greetings:

I have a crosstab query that shows peak and non-peak sales volume for each of our accounts for each day of the week over a year. The query below works:

TRANSFORM
Round(Sum(IIf([FieldName]="Beer",[BeerVol],IIf([FieldName]="Soda",[SodaVol],IIf([FieldName]="Total",[TotalVol],1))))/52) AS TheValue
SELECT tblSalesByAcctDailyRoll12Mth.AcctNbr
FROM tblXTabColHeadsVolume, tblSalesByAcctDailyRoll12Mth
GROUP BY tblSalesByAcctDailyRoll12Mth.AcctNbr
PIVOT [FieldName] & Weekday([DelDate],2) & IIf(Month([DelDate])>4 And Month([DelDate])<9,"PK","NP");

It returns:

Acct | BeerDay1PK | BeerDay1NP | BeerDay2PK ...
A1234 50

The flaw in the query is that it divides all sales volumes by 52 to providea weekly average. For my purposes, I need to divide sales during the peak period (May-Aug) by 16, and sales during the non-peak period by 32. If I replace "/52" with "IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32)" like this:

TRANSFORM
Round(Sum(IIf([FieldName]="Beer",[BeerVol],IIf([FieldName]="Soda",[SodaVol],IIf([FieldName]="Total",[TotalVol],1))))/IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32)) AS TheValue
SELECT tblSalesByAcctDailyRoll12Mth.AcctNbr
FROM tblXTabColHeadsVolume, tblSalesByAcctDailyRoll12Mth
GROUP BY tblSalesByAcctDailyRoll12Mth.AcctNbr
PIVOT [FieldName] & Weekday([DelDate],2) & IIf(Month([DelDate])>4 And Month([DelDate])<9,"PK","NP");

Access throws the error "You tried to execute a query that does not includethe specified expression 'Round(Sum(IIf([FieldName]="Beer",[BeerVol],IIf([FieldName]="Soda",[SodaVol],IIf([FieldName]="Total",[TotalVol],1))))/IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32))' as part of an aggregate function.

I can't figure out how to get the result I want. Can anyone point me in theright direction?

TIA,

Keith