IIF in crosstab expression

Question

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. 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 provide a 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 include the 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 the right direction?

TIA,

Keith

Answer 1

I have reformatted your Transform to show more clearly what is wrong:

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

As you can see, the divisor is outside the SUM(), and is no longer constant. Fortunately, diision distributes over addition, so we can change the transform to look like this:

Round(
   Sum(
      IIf([FieldName]="Beer", [BeerVol]
                  / IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32),
      IIf([FieldName]="Soda", [SodaVol]
                  / IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32),
      IIf([FieldName]="Total",[TotalVol]
                 / IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32)
                             ,1
                 / IIf(Month([DelDate])>4 And Month([DelDate])<9,16, 32)
      ) ) )
   )
) AS TheValue

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

Answer 2

That work! Thanks, Pieter.

---

Keith Gardner