CODEWITHSUNDEEP
sqlms-accesscalculated-columns
fullOfQuestions
fullOfQuestions

Reputation: 453

Access Calculated Field with IF

I have a table that tracks player stats:

Player        Pts    Reb    Ast    Blk    Stl    Tov
Jeff Teague   21     11      15     1      2      4

I want to add a calculated field that calculates the fantasy points scored by that player. The player gets:

1 fantasy point per Pts
1.25 fantasy points per Reb
1.5 fantasy points per Ast
2 fantasy points per Ast
2 fantasy points per Stl
-.5 fantasy points per Tov
1.5 fantasy points IF any two of Pts, Reb, Ast, Blk, or Stl are 10 or more
3 fantasy points IF any three of Pts, Reb, Ast, Blk, or Stl are 10 or more

So in this example he would get (21)+(11*1.25)+(15*1.5)+(2)+(2*2)-(.5*4)+(1.5)+(3) = 61.75 fantasy points. The extra 1.5 and 3 is because Pts, Ast, and Reb are all 10 or greater.

So far the calculate field I have has:

([PTS])+([TRB]*1.25)+([AST]*1.5)+([STL]*2)+([BLK]*2)-([TOV]*.5)

But I can't figure out how to include the IF statements for the last two factors.

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

It is a sort of a nasty calculation, but here is how you do it:

  [PTS]
+ [TRB] * 1.25
+ [AST] * 1.5
+ [STL] * 2
+ [BLK] * 2
- [TOV] * 0.5
+ iif(
    iif([PTS] >= 10, 1, 0)
  + iif([Reb] >= 10, 1, 0)
  + iif([AST] >= 10, 1, 0)
  + iif([Blk] >= 10, 1, 0)
  + iif([Stl] >= 10, 1, 0) = 2
  , 1.5
  , 0
  )
+ iif(
    iif([PTS] >= 10, 1, 0)
  + iif([Reb] >= 10, 1, 0)
  + iif([AST] >= 10, 1, 0)
  + iif([Blk] >= 10, 1, 0)
  + iif([Stl] >= 10, 1, 0) = 3
  , 3
  , 0
  )

That is, use iif() to convert each comparison to 0 or 1, then add these together and compare to the threshold number.

Upvotes: 2

Related Questions