IIf expression in design

[drober]

HELP Please... I am trying to build an IIf statement in my design view but
can't figure out how to get results compare to skill type. I figure I am on
the wrong track.
I have the following Skills:
NBI
Combo
iPhone
Care
TSD
FS
Each Skill has a different scale to apply to a representative's AHT(average
handle time)here is one scale:
Scale for NBI:
AHT Pts Rating
<=493 50 CE
494-554 42 FE
555-615 35 ME
616-704 28 MS

=705 0 NM

so based on the Skill type I would like to compare to the scale that
corresponds with the skill type then apply the associated points and rating.
I am trying to use this to get stat results for representatives based on
their AHT (average handle time) every month.

 

[WD Rudman]

You might want to look into the SWITCH() function

 

[drober]

haven't be able to find an example..do you have one? Thanks

 

[drober]

Mar_SkillRating: IIf(IsNull([AHT_Mar]),"
",IIf([Mar_Skill]="NBI",IIf([AHT_Mar]<494,"CE",IIf([AHT_Mar]<555,"FE",IIf([AHT_Mar]<616,"ME",IIf([AHT_Mar]<705,"MS",IIf([AHT_Mar]>=705,"NM",IIf([Mar_Skill]="Combo",IIf([AHT_Mar]<596,"CE",IIf([AHT_Mar]<625,"FE",IIf([AHT_Mar]<686,"ME",IIf([AHT_Mar]<775,"MS",IIf([AHT_Mar]>=775,"NM"," ")))))))))))))

For Rating I used the above but it only returns the NBI result. Also is
there a limit in the IIF, I wasn't able to add the IIFs for the other skills?

 

[John W. Vinson]

HELP Please... I am trying to build an IIf statement in my design view but
can't figure out how to get results compare to skill type. I figure I am on
the wrong track.
I have the following Skills:
NBI
Combo
iPhone
Care
TSD
FS
Each Skill has a different scale to apply to a representative's AHT(average
handle time)here is one scale:
Scale for NBI:
AHT Pts Rating
<=493 50 CE
494-554 42 FE
555-615 35 ME
616-704 28 MS
so based on the Skill type I would like to compare to the scale that
corresponds with the skill type then apply the associated points and rating.
I am trying to use this to get stat results for representatives based on
their AHT (average handle time) every month.

With a proper table structure and a "Non Equi Join" query, you can do this
with no IIF's or code at all. Consider a Ratings table with fields AHTLow,
AHTHigh, Skill, Pts and Rating:

AHTLow AHTHigh Skill Pts Rating
0 493 NBI 50 CE
494 554 NBI 42 FE
555 615 NBI 35 ME
616 704 NBI 28 MS
705 9999 NBI 0 NM
0 400 XYZ 50 CE

<etc, with five rows for each Skill value>.

You can join this table to the query that calculates AHT, with a SQL JOIN
clause like

ON query.AHT >= Ratings.AHTLow AND query.AHT <= Ratings.AHTHigh

Also join the employee's Skill level to the SKill field. This will look up the
appropriate pts and rating for this employee at this AHT.

 

[drober]

Thank you SOOO much...I will try this shortly and let you know how it works.
Thanks again for your assistance!