IIF in crosstab expression

# IIF in crosstab expression

• Dienstag, 4. Dezember 2012 15:32

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
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
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

### Alle Antworten

• Dienstag, 4. Dezember 2012 15:52

```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