IIf expression in design

  • Thread starter Thread starter drober
  • Start date Start date
D

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
Click to expand...
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.
 
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?
 
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.
Click to expand...

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.
 
Thank you SOOO much...I will try this shortly and let you know how it works.
Thanks again for your assistance!
 
Back
Top