IIF as a crosstab row heading

[Guest]

I have a crosstab query which is based on another query. I want to calculate
a row heading that is either an avg or sum depending on how the Iif is
evaluated. Here is the expression:

Field = Total Of Qty: IIf([Goal ID]="05a" Or [Goal
ID]="05b",Avg([Qty]),Sum([Qty]))
Total = Expression
Crosstab = Row Heading

This is the error I get:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Is there something stupid I'm doing?

 

[MGFoster]

I have a crosstab query which is based on another query. I want to calculate
a row heading that is either an avg or sum depending on how the Iif is
evaluated. Here is the expression:

Field = Total Of Qty: IIf([Goal ID]="05a" Or [Goal
ID]="05b",Avg([Qty]),Sum([Qty]))
Total = Expression
Crosstab = Row Heading

This is the error I get:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Is there something stupid I'm doing?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, you're trying to put 2 different types of calculations in one
column. You should make 2 columns: Average & Total.

Think about it. Your Field output would look like this:

Field
======
200
25
30
5000

Which is the average and which is the total?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9tH4echKqOuFEgEQJ21wCbBLCyf9gPheHP3R7B++HDs7YqVMAAmQHx
tlOKgBieJvgi6+oEhpY4X6ap
=xZXK
-----END PGP SIGNATURE-----

 

[Guest]

If you notice in the IIF, I always will know which is an avg and which is a
sum based on the Goal ID. My report mostly sums quantities, but there are
two Goals (05a and 05b) which are response times where I need an average.
The output is either monthly and YTD sum or avg (for the two response time
calcs).

I have a crosstab query which is based on another query. I want to calculate
a row heading that is either an avg or sum depending on how the Iif is
evaluated. Here is the expression:

Field = Total Of Qty: IIf([Goal ID]="05a" Or [Goal
ID]="05b",Avg([Qty]),Sum([Qty]))
Total = Expression
Crosstab = Row Heading

This is the error I get:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Is there something stupid I'm doing?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, you're trying to put 2 different types of calculations in one
column. You should make 2 columns: Average & Total.

Think about it. Your Field output would look like this:

Field
======
200
25
30
5000

Which is the average and which is the total?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9tH4echKqOuFEgEQJ21wCbBLCyf9gPheHP3R7B++HDs7YqVMAAmQHx
tlOKgBieJvgi6+oEhpY4X6ap
=xZXK
-----END PGP SIGNATURE-----

 

[Guest]

I think the problem here is that Access looks at the outside function (IIF)
and sees it's not an aggregate function. It does not evaluate the case to
see if the result(s) are aggregate functions. I think if you through a
redundant aggregate around the whole thing (say min/max or first/last) it
should work.

I would test by doing a column of sum, a column of avg, and a column of this
expression and verifying it picks the appropriate one and calculates properly.

If you notice in the IIF, I always will know which is an avg and which is a
sum based on the Goal ID. My report mostly sums quantities, but there are
two Goals (05a and 05b) which are response times where I need an average.
The output is either monthly and YTD sum or avg (for the two response time
calcs).

I have a crosstab query which is based on another query. I want to calculate
a row heading that is either an avg or sum depending on how the Iif is
evaluated. Here is the expression:

Field = Total Of Qty: IIf([Goal ID]="05a" Or [Goal
ID]="05b",Avg([Qty]),Sum([Qty]))
Total = Expression
Crosstab = Row Heading

This is the error I get:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Is there something stupid I'm doing?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, you're trying to put 2 different types of calculations in one
column. You should make 2 columns: Average & Total.

Think about it. Your Field output would look like this:

Field
======
200
25
30
5000

Which is the average and which is the total?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9tH4echKqOuFEgEQJ21wCbBLCyf9gPheHP3R7B++HDs7YqVMAAmQHx
tlOKgBieJvgi6+oEhpY4X6ap
=xZXK
-----END PGP SIGNATURE-----