IIF statement expression help

[YST]

Hi all,

I am trying to do a weighted average calculation and need to use an IIF statement to do it.

Here is what I have so far:

TotalSum: IIf([Transactions]![Shares]>0,Sum([Price]*[Shares])/Sum([Transactions]![Shares]),+0)

Okay, the first part:

Do the true part of the statement if Shares>0. This signifies a BUY, a negative signifies a SELL or a MOVE.

If True: Add this row to the weighted average calculation. I have tested this part prior to adding the IIF statement and it worked as expected.

If False: Essentially skip the row or do nothing and go onto the next row, but how do I signify this exactly?

Many thanks for the help!

Rob

 

[The_Doc_Man]

Sum in an IIF doesn't work.

TotalSum: IIf([Transactions]![Shares]>0,Sum([Price]*[Shares])/Sum([Transactions]![Shares]),+0)

Depending on context, you need one of two things to have been true already.

First option: Use DSum rather than Sum. Look up DSum in the Help files. Performance would (to put it gently) suck. But it would be trivial to implement.

Second option: Since a multiplication/division class operator is involved, associativity is not possible. That is, Sum(A) + Sum(B) is equal to Sum(A+B). BUT... Sum(A)/Sum(B) is NOT equal to Sum(A/B). If you really wanted the ratio as you stated, there are ways to compute it with a Select query on top of a Select Summation query.

I.e. Build a Summation query for the Transactions table. Build another for the sum of the Price*Shares number. Then use the results of those queries as the input records for a second Select query that computes your ratio.

By the way, I suspect that you glossed over the [Price] issue in your description. Is that also part of a table? If so, it might add just a little more spice to the computation.

 

[YST]

Any idea why the expression would work in the first place? According to what you've said, it shouldn't have produced the correct results.

 

[namliam]

How about not doing it with a IIF at all but putting into the where clause:

[Transactions]![Shares]>0

Regards

 

[The_Doc_Man]

First, namliam's answer is a good one.

Second, I long ago gave up wondering why Access did something that it shouldn't have been able to do.

All I can tell you is that the semantics of the SUM and the DSUM functions are totally different.

SUM might well have worked correctly - but if there was a GROUP-BY anywhere near it, its behavior would be totally suspect because of the need to specify the "totals" behavior - over each group or over all groups, for example.

I suggested you avoid SUM precisely because its semantics tend to be a bit vague sometimes, whereas DSUM or an explicit (and SEPARATE) SUM query that you wrote yourself (and therefore know exactly what is in it) would tend to be unequivocal.