Nested Iif Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the SQL view query to calculate grades for a class and want to set it up so that if the student scores below a minimum grade for a certain number of areas, the students gets assigned a mandatory grade.

i.e., Iif (2 of [Name],[Topic],[Motivation],[Objectives],[Overview] are <2.8, 2.0, ([Name}+[Topic]+[Motivation]+[Objectives]+[Overview])/5)

I realize the syntax for the logic statement won't work, but that's basically what I would like it to do. Is there a simpler way than nested Iif statements, as that becomes very cumbersome very quickly
 
Frank,

Before trying to find a specific solution to the immediate question, I
need to ask you a question... Is your database design set in
concrete, or could you consider a revision? The reason it is
cumbersome at present is because your table structure is flawed, and
if you are able to correct this, your future paracetamol expenditure
will be slashed.

- Steve Schapel, Microsoft Access MVP
 
It can be done, but I think you might be better off evaluating your data structure.

IIF(Abs(Nz([Name])<2.8 +
Nz([Topic)<2.8 + Nz(Motivation) < 2.8
+ ...) > 1,
2.0,
[Name]+[Topic]+[Motivation]+[Objectives]+[Overview])/5)

This checks to see if a field is less than 2.8 and if it is returns -1 (True),
it then adds the values together and sees if there is more than one of them that
tested true. If so, return the value 2.0, otherwise calculate the average score.

By the way if any of the values is null, then your formula will fail unless you
use the NZ function around each of them, or use more IIF statements to return zero.
 
Good reply... It got me to look up "paracetamol" since it isn't part of my
vocabulary.

--
Duane Hookom
MS Access MVP


Steve Schapel said:
Frank,

Before trying to find a specific solution to the immediate question, I
need to ask you a question... Is your database design set in
concrete, or could you consider a revision? The reason it is
cumbersome at present is because your table structure is flawed, and
if you are able to correct this, your future paracetamol expenditure
will be slashed.

- Steve Schapel, Microsoft Access MVP
Click to expand...
set it up so that if the student scores below a minimum grade for a certain
number of areas, the students gets assigned a mandatory grade.
i.e., Iif (2 of [Name],[Topic],[Motivation],[Objectives],[Overview] are <2.8, 2.0, ([Name}+[Topic]+[Motivation]+[Objectives]+[Overview])/5)

I realize the syntax for the logic statement won't work, but that's
Click to expand...
Click to expand...
basically what I would like it to do. Is there a simpler way than nested
Iif statements, as that becomes very cumbersome very quickly
 
Sorry... just assumed, somehow, that as regards headache medication,
it would be a worldwide staple. :-)

- Steve Schapel, Microsoft Access MVP
 
On this side of the pond (Northwest Wisconsin) we call it Aspirin or
"Tylenol Headache #12 : Someone created this spreadsheet like Access
application and now I have to create queries..."
 
Thanks for the help... As far as the data structure goes, I'm afraid I'm not super Access saavy
 
Back
Top