Determine result based on mixed number of variables

[PRMiller]

Good afternoon,

I have been struggling with this issue for a while and am finally being smart enough to ask for help.

I have a database application used to measure performance for customer service reps. One aspect of their performance measurement is productivity. Reps take a variety of phone calls. For our purposes, let's call them Type 1, Type 2, Type 3, Type 4, Type 5, etc up through Type 9.

There are talk-time standards associated with each call type, but they vary. For purposes of this question, let's set those standards as follows:

Type 1: 214 seconds
Type 2: 540 seconds
Type 3: 530 seconds
Type 4: 540 seconds
Type 5: 540 seconds
Type 6: 710 seconds
Type 7: 540 seconds
Type 8: 540 seconds
Type 9: 300 seconds

Reps may have a talk-time at or below (faster) the standard for the particular call type to be considered "Meeting."

Reps often take 3, 4, or even 5 different types of phone calls. I have successfully created a module that says if you are at or below the standard for the majority of the types of calls you take (ie - 299 seconds for Type 9, which is 80% of your calls), then you are considered "Meeting" for Productivity as a whole, REGARDLESS of whether or not you met standards for the other call types.

(This principle was established to reward reps for the types of calls they take the majority of the time, not penalize them if they took 1 call in Type 1 and it was below standards.)

Here's where it gets complicated:

We have already established the standard for "Meeting" expectations. We want to tell the rep that, if they are at or below the standard for EVERY type of call they took, then they are now considered "Exceeding" expectations. Here's an example:

Rep 1
=====
900 calls in Type 1 with a talk-time of 205 seconds
100 calls in Type 6 with a talk-time of 1200 seconds
"Meeting" expectations

Rep 2
=====
900 calls in Type 1 with a talk-time of 205 seconds
100 calls in Type 6 with a talk-time of 700 seconds
"Exceeds" expectations

I have been unable to program the module to accurately return "Exceeds" on a consistent basis. It is also extremely inefficent.

Any advice from programmers out there?

Thanks much!
Paul

 

[SkipVought]

Paul,

How about posting the code that you have already and describe how it fails to meet the criteria.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PRMiller]

Ok, here's the code. I wrestled with posting it, because it's huge. Here's what we did: We established "high" and "low" threshholds. While our goal was, say, 300 seconds for one call type, we set a percentage where we said, "Ok, we know that 300 is our enterprise goal. We're going to be nice and give you a treshhold over and above that since this is new." So, for example, the upper threshhold for Type One might be 10% higher, or 330 seconds.

To get to this ridiculous overall number, we took the sum total of calls for type one, multiplied that by the upper-treshhold talk time, then did the same thing for two, three, etc, like so:

Type 1 x Upper Talk Time = Gross Talk Time Goal 1
Type 2 x Upper Talk Time = Gross Talk Time Goal 2
Type 3 x Upper Talk Time = Gross Talk Time Goal 3
etc, etc

We then summed all the "Gross Talk Time Goals" to come up with a "Total Gross Talk Time Goal" which, of course, was an obscenely high number.

We then took the ACTUAL talk time for each:

Type 1 x Actual Talk Time = Gross Talk Time 1
Type 2 x Actual Talk Time = Gross Talk Time 2
Type 3 x Actual Talk Time = Gross Talk Time 3
etc, etc

We then summed all the "Gross Talk Times" to come up with a "Total Gross Talk Time."

As long as the Total Gross Talk Time was at or less than the Total Gross Talk Time Goal, the rep "Exceeded."

Well, screw that. The calculations might work, but we're scrapping it because it doesn't track with what we said.

What I'd LIKE to do is find a way to loop through all of the types to see if they meet. If all meet, change them to exceed. It's GOT to be easier than what we did above.

I'll post the original code in the next e-mail.

 

[PRMiller]

Sub Productivity()

    Dim lngBucketHT(1 To 20) As Long
    Dim lngTotalHT As Long
    Dim intBucketGoal(1 To 20) As Integer
    Dim lngSumBucketGoal(1 To 20) As Long
    Dim lngTotadblHighGoal As Long
    Dim lTotadblLowGoal As Long
    Dim dblHighPct(1 To 20) As Double
    Dim dblLowPct(1 To 20) As Double
    Dim dblHighGoal(1 To 20) As Double
    Dim dblLowGoal(1 To 20) As Double
    Dim i As Integer

    If Me.txtProdBucket1Calls.Value = 0 Then
        Me.chkIncludeBucket1.Value = 0
        Me.lblProdBucket1.Visible = False
    Else
        Me.chkIncludeBucket1.Value = -1
        Me.txtProdBucket1Calls.Visible = True
        Me.txtProdBucket1Min.Visible = True
        Me.txtProdBucket1Min.Value = Format((Me.txtProdBucket1AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket1Pct.Visible = True
        Me.txtProdBucket1Pct = Me.txtProdBucket1Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket2Calls.Value = 0 Then
        Me.chkIncludeBucket2.Value = 0
        Me.lblProdBucket2.Visible = False
    Else
        Me.chkIncludeBucket2.Value = -1
        Me.txtProdBucket2Calls.Visible = True
        Me.txtProdBucket2Min.Visible = True
        Me.txtProdBucket2Min.Value = Format((Me.txtProdBucket2AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket2Pct.Visible = True
        Me.txtProdBucket2Pct = Me.txtProdBucket2Calls / Me.txtTotalCalls.Value
    End If
    
     If Me.txtProdBucket3Calls.Value = 0 Then
        Me.chkIncludeBucket3.Value = 0
        Me.lblProdBucket3.Visible = False
    Else
        Me.chkIncludeBucket3.Value = -1
        Me.txtProdBucket3Calls.Visible = True
        Me.txtProdBucket3Min.Visible = True
        Me.txtProdBucket3Min.Value = Format((Me.txtProdBucket3AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket3Pct.Visible = True
        Me.txtProdBucket3Pct = Me.txtProdBucket3Calls / Me.txtTotalCalls.Value
     End If
    
    If Me.txtProdBucket4Calls.Value = 0 Then
        Me.chkIncludeBucket4.Value = 0
        Me.lblProdBucket4.Visible = False
    Else
        Me.chkIncludeBucket4.Value = -1
        Me.txtProdBucket4Calls.Visible = True
        Me.txtProdBucket4Min.Visible = True
        Me.txtProdBucket4Min.Value = Format((Me.txtProdBucket4AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket4Pct.Visible = True
        Me.txtProdBucket4Pct = Me.txtProdBucket4Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket5Calls.Value = 0 Then
        Me.chkIncludeBucket5.Value = 0
        Me.lblProdBucket5.Visible = False
    Else
        Me.chkIncludeBucket5.Value = -1
        Me.txtProdBucket5Calls.Visible = True
        Me.txtProdBucket5Min.Visible = True
        Me.txtProdBucket5Min.Value = Format((Me.txtProdBucket5AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket5Pct.Visible = True
        Me.txtProdBucket5Pct = Me.txtProdBucket5Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket6Calls.Value = 0 Then
        Me.chkIncludeBucket6.Value = 0
        Me.lblProdBucket6.Visible = False
    Else
        Me.chkIncludeBucket6.Value = -1
        Me.txtProdBucket6Calls.Visible = True
        Me.txtProdBucket6Min.Visible = True
        Me.txtProdBucket6Min.Value = Format((Me.txtProdBucket6AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket6Pct.Visible = True
        Me.txtProdBucket6Pct = Me.txtProdBucket6Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket7Calls.Value = 0 Then
        Me.chkIncludeBucket7.Value = 0
        Me.lblProdBucket7.Visible = False
    Else
        Me.chkIncludeBucket7.Value = -1
        Me.txtProdBucket7Calls.Visible = True
        Me.txtProdBucket7Min.Visible = True
        Me.txtProdBucket7Min.Value = Format((Me.txtProdBucket7AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket7Pct.Visible = True
        Me.txtProdBucket7Pct = Me.txtProdBucket7Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket8Calls.Value = 0 Then
        Me.chkIncludeBucket8.Value = 0
        Me.lblProdBucket8.Visible = False
    Else
        Me.chkIncludeBucket8.Value = -1
        Me.txtProdBucket8Calls.Visible = True
        Me.txtProdBucket8Min.Visible = True
        Me.txtProdBucket8Min.Value = Format((Me.txtProdBucket8AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket8Pct.Visible = True
        Me.txtProdBucket8Pct = Me.txtProdBucket8Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket9Calls.Value = 0 Then
        Me.chkIncludeBucket9.Value = 0
        Me.lblProdBucket9.Visible = False
    Else
        Me.chkIncludeBucket9.Value = -1
        Me.txtProdBucket9Calls.Visible = True
        Me.txtProdBucket9Min.Visible = True
        Me.txtProdBucket9Min.Value = Format((Me.txtProdBucket9AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket9Pct.Visible = True
        Me.txtProdBucket9Pct = Me.txtProdBucket9Calls / Me.txtTotalCalls.Value
    End If
   
    If Me.txtProdBucket10Calls.Value = 0 Then
        Me.chkIncludeBucket10.Value = 0
        Me.lblProdBucket10.Visible = False
    Else
        Me.chkIncludeBucket10.Value = -1
        Me.txtProdBucket10Calls.Visible = True
        Me.txtProdBucket10Min.Visible = True
        Me.txtProdBucket10Min.Value = Format((Me.txtProdBucket10AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket10Pct.Visible = True
        Me.txtProdBucket10Pct = Me.txtProdBucket10Calls / Me.txtTotalCalls.Value
    End If
   
    If Me.txtProdBucket11Calls.Value = 0 Then
        Me.chkIncludeBucket11.Value = 0
        Me.lblProdBucket11.Visible = False
    Else
        Me.chkIncludeBucket11.Value = -1
        Me.txtProdBucket11Calls.Visible = True
        Me.txtProdBucket11Min.Visible = True
        Me.txtProdBucket11Min.Value = Format((Me.txtProdBucket11AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket11Pct.Visible = True
        Me.txtProdBucket11Pct = Me.txtProdBucket11Calls / Me.txtTotalCalls.Value
    End If
    
     If Me.txtProdBucket12Calls.Value = 0 Then
        Me.chkIncludeBucket12.Value = 0
        Me.lblProdBucket12.Visible = False
    Else
        Me.chkIncludeBucket12.Value = -1
        Me.txtProdBucket12Calls.Visible = True
        Me.txtProdBucket12Min.Visible = True
        Me.txtProdBucket12Min.Value = Format((Me.txtProdBucket12AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket12Pct.Visible = True
        Me.txtProdBucket12Pct = Me.txtProdBucket12Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket13Calls.Value = 0 Then
        Me.chkIncludeBucket13.Value = 0
        Me.lblProdBucket13.Visible = False
    Else
        Me.chkIncludeBucket13.Value = -1
        Me.txtProdBucket13Calls.Visible = True
        Me.txtProdBucket13Min.Visible = True
        Me.txtProdBucket13Min.Value = Format((Me.txtProdBucket13AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket13Pct.Visible = True
        Me.txtProdBucket13Pct = Me.txtProdBucket13Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket14Calls.Value = 0 Then
        Me.chkIncludeBucket14.Value = 0
        Me.lblProdBucket14.Visible = False
    Else
        Me.chkIncludeBucket14.Value = -1
        Me.txtProdBucket14Calls.Visible = True
        Me.txtProdBucket14Min.Visible = True
        Me.txtProdBucket14Min.Value = Format((Me.txtProdBucket14AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket14Pct.Visible = True
        Me.txtProdBucket14Pct = Me.txtProdBucket14Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket15Calls.Value = 0 Then
        Me.chkIncludeBucket15.Value = 0
        Me.lblProdBucket15.Visible = False
     Else
        Me.chkIncludeBucket15.Value = -1
        Me.txtProdBucket15Calls.Visible = True
        Me.txtProdBucket15Min.Visible = True
        Me.txtProdBucket15Min.Value = Format((Me.txtProdBucket15AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket15Pct.Visible = True
        Me.txtProdBucket15Pct = Me.txtProdBucket15Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.txtProdBucket16Calls.Value = 0 Then
        Me.chkIncludeBucket16.Value = 0
        Me.lblProdBucket16.Visible = False
     Else
        Me.chkIncludeBucket16.Value = -1
        Me.txtProdBucket16Calls.Visible = True
        Me.txtProdBucket16Min.Visible = True
        Me.txtProdBucket16Min.Value = Format((Me.txtProdBucket16AHT.Value / 24 / 60 / 60), "n:ss")
        Me.txtProdBucket16Pct.Visible = True
        Me.txtProdBucket16Pct = Me.txtProdBucket16Calls / Me.txtTotalCalls.Value
    End If
    
    If Me.chkIncludeBucket1.Value = -1 Then
        lngBucketHT(1) = Me.txtProdBucket1AHT * Me.txtProdBucket1Calls
    End If
    
    If Me.chkIncludeBucket2.Value = -1 Then
        lngBucketHT(2) = Me.txtProdBucket2AHT * Me.txtProdBucket2Calls
    End If
   
    If Me.chkIncludeBucket3.Value = -1 Then
        lngBucketHT(3) = Me.txtProdBucket3AHT * Me.txtProdBucket3Calls
    End If
   
    If Me.chkIncludeBucket4.Value = -1 Then
        lngBucketHT(4) = Me.txtProdBucket4AHT * Me.txtProdBucket4Calls
    End If
    
    If Me.chkIncludeBucket5.Value = -1 Then
        lngBucketHT(5) = Me.txtProdBucket5AHT * Me.txtProdBucket5Calls
    End If
    
    If Me.chkIncludeBucket6.Value = -1 Then
        lngBucketHT(6) = Me.txtProdBucket6AHT * Me.txtProdBucket6Calls
    End If
    
    If Me.chkIncludeBucket7.Value = -1 Then
        lngBucketHT(7) = Me.txtProdBucket7AHT * Me.txtProdBucket7Calls
    End If
    
    If Me.chkIncludeBucket8.Value = -1 Then
        lngBucketHT(8) = Me.txtProdBucket8AHT * Me.txtProdBucket8Calls
    End If
    
    If Me.chkIncludeBucket9.Value = -1 Then
        lngBucketHT(9) = Me.txtProdBucket9AHT * Me.txtProdBucket9Calls
    End If
    
    If Me.chkIncludeBucket10.Value = -1 Then
        lngBucketHT(10) = Me.txtProdBucket10AHT * Me.txtProdBucket10Calls
    End If
    
    If Me.chkIncludeBucket11.Value = -1 Then
        lngBucketHT(11) = Me.txtProdBucket11AHT * Me.txtProdBucket11Calls
    End If
    
    If Me.chkIncludeBucket12.Value = -1 Then
        lngBucketHT(12) = Me.txtProdBucket12AHT * Me.txtProdBucket12Calls
    End If
    
    If Me.chkIncludeBucket13.Value = -1 Then
        lngBucketHT(13) = Me.txtProdBucket13AHT * Me.txtProdBucket13Calls
    End If
    
    If Me.chkIncludeBucket14.Value = -1 Then
        lngBucketHT(14) = Me.txtProdBucket14AHT * Me.txtProdBucket14Calls
    End If
    
    If Me.chkIncludeBucket15.Value = -1 Then
        lngBucketHT(15) = Me.txtProdBucket15AHT * Me.txtProdBucket15Calls
    End If
    
    If Me.chkIncludeBucket16.Value = -1 Then
        lngBucketHT(16) = Me.txtProdBucket16AHT * Me.txtProdBucket16Calls
    End If

    lngTotalHT = lngBucketHT(1) + lngBucketHT(2) + lngBucketHT(3) + lngBucketHT(4) + lngBucketHT(5)
    lngTotalHT = lngTotalHT + lngBucketHT(6) + lngBucketHT(7) + lngBucketHT(8) + lngBucketHT(9)
    lngTotalHT = lngTotalHT + lngBucketHT(10) + lngBucketHT(11) + lngBucketHT(12) + lngBucketHT(13)
    lngTotalHT = lngTotalHT + lngBucketHT(14) + lngBucketHT(15) + lngBucketHT(16) + lngBucketHT(17)
    lngTotalHT = lngTotalHT + lngBucketHT(18) + lngBucketHT(19) + lngBucketHT(20)
    
    intBucketGoal(1) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 1"))
    intBucketGoal(2) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 2"))
    intBucketGoal(3) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 3"))
    intBucketGoal(4) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 4"))
    intBucketGoal(5) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 5"))
    intBucketGoal(6) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 6"))
    intBucketGoal(7) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 7"))
    intBucketGoal(8) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 8"))
    intBucketGoal(9) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 9"))
    intBucketGoal(10) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 10"))
    intBucketGoal(11) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 11"))
    intBucketGoal(12) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 12"))
    intBucketGoal(13) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 13"))
    intBucketGoal(14) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 14"))
    intBucketGoal(15) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 15"))
    intBucketGoal(16) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 16"))
    intBucketGoal(17) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 17"))
    intBucketGoal(18) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 18"))
    intBucketGoal(19) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 19"))
    intBucketGoal(20) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 20"))
    
    dblHighPct(1) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 1"))
    dblLowPct(1) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 1"))
    dblHighPct(2) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 2"))
    dblLowPct(2) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 2"))
    dblHighPct(3) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 3"))
    dblLowPct(3) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 3"))
    dblHighPct(4) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 4"))
    dblLowPct(4) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 4"))
    dblHighPct(5) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 5"))
    dblLowPct(5) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 5"))
    dblHighPct(6) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 6"))
    dblLowPct(6) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 6"))
    dblHighPct(7) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 7"))
    dblLowPct(7) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 7"))
    dblHighPct(8) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 8"))
    dblLowPct(8) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 8"))
    dblHighPct(9) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 9"))
    dblLowPct(9) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 9"))
    dblHighPct(10) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 10"))
    dblLowPct(10) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 10"))
    dblHighPct(11) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 11"))
    dblLowPct(11) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 11"))
    dblHighPct(12) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 12"))
    dblLowPct(12) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 12"))
    dblHighPct(13) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 13"))
    dblLowPct(13) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 13"))
    dblHighPct(14) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 14"))
    dblLowPct(14) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 14"))
    dblHighPct(15) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 15"))
    dblLowPct(15) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 15"))
    dblHighPct(16) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 16"))
    dblLowPct(16) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 16"))
    dblHighPct(17) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 17"))
    dblLowPct(17) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 17"))
    dblHighPct(18) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 18"))
    dblLowPct(18) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 18"))
    dblHighPct(19) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 19"))
    dblLowPct(19) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 19"))
    dblHighPct(20) = Nz(DLookup("HighPct", "tblPropertiesBuckets", "BucketNumber = 20"))
    dblLowPct(20) = Nz(DLookup("LowPct", "tblPropertiesBuckets", "BucketNumber = 20"))
    
    If Not IsNull(Me.txtProdBucket1Calls) Then
        lngSumBucketGoal(1) = intBucketGoal(1) * Me.txtProdBucket1Calls
    End If
    
    If Not IsNull(Me.txtProdBucket2Calls) Then
        lngSumBucketGoal(2) = intBucketGoal(2) * Me.txtProdBucket2Calls
    End If
    
    If Not IsNull(Me.txtProdBucket3Calls) Then
        lngSumBucketGoal(3) = intBucketGoal(3) * Me.txtProdBucket3Calls
    End If
    
    If Not IsNull(Me.txtProdBucket4Calls) Then
        lngSumBucketGoal(4) = intBucketGoal(4) * Me.txtProdBucket4Calls
    End If
    
    If Not IsNull(Me.txtProdBucket5Calls) Then
        lngSumBucketGoal(5) = intBucketGoal(5) * Me.txtProdBucket5Calls
    End If
    
    If Not IsNull(Me.txtProdBucket6Calls) Then
        lngSumBucketGoal(6) = intBucketGoal(6) * Me.txtProdBucket6Calls
    End If
    
    If Not IsNull(Me.txtProdBucket7Calls) Then
        lngSumBucketGoal(7) = intBucketGoal(7) * Me.txtProdBucket7Calls
    End If
    
    If Not IsNull(Me.txtProdBucket8Calls) Then
        lngSumBucketGoal(8) = intBucketGoal(8) * Me.txtProdBucket8Calls
    End If
    
    If Not IsNull(Me.txtProdBucket9Calls) Then
        lngSumBucketGoal(9) = intBucketGoal(9) * Me.txtProdBucket9Calls
    End If
    
    If Not IsNull(Me.txtProdBucket10Calls) Then
        lngSumBucketGoal(10) = intBucketGoal(10) * Me.txtProdBucket10Calls
    End If
        
    If Not IsNull(Me.txtProdBucket11Calls) Then
        lngSumBucketGoal(11) = intBucketGoal(11) * Me.txtProdBucket11Calls
    End If
        
    If Not IsNull(Me.txtProdBucket12Calls) Then
        lngSumBucketGoal(12) = intBucketGoal(12) * Me.txtProdBucket12Calls
    End If
        
    If Not IsNull(Me.txtProdBucket13Calls) Then
        lngSumBucketGoal(13) = intBucketGoal(13) * Me.txtProdBucket13Calls
    End If
    
    If Not IsNull(Me.txtProdBucket14Calls) Then
        lngSumBucketGoal(14) = intBucketGoal(14) * Me.txtProdBucket14Calls
    End If
        
    If Not IsNull(Me.txtProdBucket15Calls) Then
        lngSumBucketGoal(15) = intBucketGoal(15) * Me.txtProdBucket15Calls
    End If
        
    If Not IsNull(Me.txtProdBucket16Calls) Then
        lngSumBucketGoal(16) = intBucketGoal(16) * Me.txtProdBucket16Calls
    End If
        
    For i = 1 To 20
        dblHighGoal(i) = lngSumBucketGoal(i) + (lngSumBucketGoal(i) * dblHighPct(i))
        If dblLowPct(i) = 1 Then
            dblLowGoal(i) = 0
        Else
            dblLowGoal(i) = lngSumBucketGoal(i) - (lngSumBucketGoal(i) * dblLowPct(i))
        End If
    Next i
    
    For i = 1 To 20
        lngTotadblHighGoal = lngTotadblHighGoal + dblHighGoal(i)
        lTotadblLowGoal = lTotadblLowGoal + dblLowGoal(i)
    Next i
    
    If lngTotalHT <= lngTotadblHighGoal Then
        If lngTotalHT >= lTotadblLowGoal Then
            'Me.chkProdOverallExceeds.Value = 0
            Me.chkProdOverallExceeds.Value = -1
            Me.chkProdOverallMeets.Value = 0
            Me.chkProdOverallNeeds.Value = 0
    
            If Me.chkIncludeBucket1.Value = -1 Then
                If lngBucketHT(1) <= dblHighGoal(1) Then
                    If lngBucketHT(1) >= dblLowGoal(1) Then
                        Me.chkProdOverallExceeds.Value = -1
                        'Me.chkProdOverallMeets.Value = 0
                        Me.chkProdOverallMeets.Value = 0
                    Else
                        Me.chkProdOverallExceeds.Value = 0
                        Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
    
            If Me.chkIncludeBucket2.Value = -1 Then
                If lngBucketHT(2) <= dblHighGoal(2) Then
                    If lngBucketHT(2) >= dblLowGoal(2) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
    
            If Me.chkIncludeBucket3.Value = -1 Then
                If lngBucketHT(3) <= dblHighGoal(3) Then
                    If lngBucketHT(3) >= dblLowGoal(3) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
    
            If Me.chkIncludeBucket4.Value = -1 Then
                If lngBucketHT(4) <= dblHighGoal(4) Then
                    If lngBucketHT(4) >= dblLowGoal(4) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
   
            If Me.chkIncludeBucket5.Value = -1 Then
                If lngBucketHT(5) <= dblHighGoal(5) Then
                    If lngBucketHT(5) >= dblLowGoal(5) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
    
            If Me.chkIncludeBucket6.Value = -1 Then
                If lngBucketHT(6) <= dblHighGoal(6) Then
                    If lngBucketHT(6) >= dblLowGoal(6) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If
    
            If Me.chkIncludeBucket7.Value = -1 Then
                If lngBucketHT(7) <= dblHighGoal(7) Then
                    If lngBucketHT(7) >= dblLowGoal(7) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket8.Value = -1 Then
                If lngBucketHT(8) <= dblHighGoal(8) Then
                    If lngBucketHT(8) >= dblLowGoal(8) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket9.Value = -1 Then
                If lngBucketHT(9) <= dblHighGoal(9) Then
                    If lngBucketHT(9) >= dblLowGoal(9) Then
                        If Me.chkProdOverallExceeds.Value = -1 Then
                            Me.chkProdOverallExceeds.Value = -1
                            Me.chkProdOverallMeets.Value = 0
                        Else
                            Me.chkProdOverallExceeds.Value = 0
                            Me.chkProdOverallMeets.Value = -1
                        End If
                    Else
                       Me.chkProdOverallExceeds.Value = 0
                       Me.chkProdOverallMeets.Value = -1
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket10.Value = -1 Then
                If lngBucketHT(10) <= dblHighGoal(10) Then
                    If lngBucketHT(10) >= dblLowGoal(10) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket11.Value = -1 Then
                If lngBucketHT(11) <= dblHighGoal(11) Then
                    If lngBucketHT(11) >= dblLowGoal(11) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket12.Value = -1 Then
                If lngBucketHT(12) <= dblHighGoal(12) Then
                    If lngBucketHT(12) >= dblLowGoal(12) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket13.Value = -1 Then
                If lngBucketHT(13) <= dblHighGoal(13) Then
                    If lngBucketHT(13) >= dblLowGoal(13) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket14.Value = -1 Then
                If lngBucketHT(14) <= dblHighGoal(14) Then
                    If lngBucketHT(14) >= dblLowGoal(14) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

            If Me.chkIncludeBucket15.Value = -1 Then
                If lngBucketHT(15) <= dblHighGoal(15) Then
                    If lngBucketHT(15) >= dblLowGoal(15) Then
                        Me.chkProdOverallExceeds.Value = -1
                        Me.chkProdOverallMeets.Value = 0
                    End If
                Else
                    Me.chkProdOverallExceeds.Value = 0
                    Me.chkProdOverallMeets.Value = -1
                End If
            End If

        Else 'If total HT is less than total HT high goal but NOT more than total HT low goal
            Me.chkProdOverallExceeds.Value = 0
            Me.chkProdOverallMeets.Value = 0
            Me.chkProdOverallNeeds.Value = -1
        End If
    Else ' If total HT is NOT less than total HT high goal
        Me.chkProdOverallExceeds.Value = 0
        Me.chkProdOverallMeets.Value = 0
        Me.chkProdOverallNeeds.Value = -1
    End If
    
End Sub

 

[MichaelRed]

Do you use /munderstand SQL? The QBE in Ms. A? An Aggregate query of so would give much of the results which you appear (I say appear as I certainly did not attempt to exhaustively review the mountain of code) to be calculating. Further, I would use an average for each call, and 'benchmark' the the performance aginst that average, not attempt to evaluate each call individually, so the calc would to just a small procedure using the few aggregats queries, not the exhaustive and repetitious code.





MichaelRed
mlred@verizon.net

 

[PRMiller]

Hi Michael

Thanks for the reply. I have considered revisiting this with queries as my experience has increased siginificantly since I initially wrote the module. We do average all of the calls for a particular call type (ie - 600 calls for Type 1, 300 calls for Type 2, etc).

The challenges I face are two-fold:

1) How can I return a value based soley on the results of the largest call type group, which could vary? For example, if the rep meets the standard in Type 1, where they took the largest amount of calls, I'd show them as "Meeting Expectations," even if they did a horrible job on Types 2 or 3. This would be an easy fix, except for the fact that the call type with the largest amount of calls varies and is never predictable. (Type 1 could be the largest for Rep A, while Type 6 could be the largest for Rep B).

2) This might be the easier of the two challenges: Now, if the rep "Meets Expectations" for all Call Types, let's return an overall value of "Exceeds."

I'm really wrestling with how set Access to automatically detect which call type is the largest and use that to return the Productivity Rating, as outlined in Step 1.

Thoughts (I know, this is a mess!)?

Thanks again,
Paul

 

[MichaelRed]

Without the db stgructure, I cannot really offer more that platitudes and generalizations. That said, computing the average time per type (from a reasonably normalized recordset) should require only a couple of queries (sum of time by type divided by count of calls per type), which (again- using reasonable normalized recordsets) should be trivial. Comparing these to the threshold values (another recordset?) seems like it returns the boolean (meets criteria), so the only renmaining issue is wheather the set of compares are all "true". If, as I presuppose, this is all accomplished in recordsets, a simple calculated column summing the booleans suffices to complete the exercise. Somewhere in these fora there is a small function (basRowSum) which should do that trick rather nicely.


There are also function for the Min. Max and other standard function (basMaxVal) which can be retrieved using the search feature. Used in conjunction with the largest "aggregate (countbytype) query easily determin the largest "Type".





MichaelRed

 

[PRMiller]

Thanks again, and that set off a lightbulb in my head. The average time per type has already been calculated and is stored in a table, so we don't need to worry about that step. Also, we can already determine which call types "meet" the standard and which ones do not.

I will use what you mentioned in the last paragraph (basMaxVal) to determine which call type is the largest, then dicate my overall results based on that max val as compared to the standard.

After I do that, I'll probably create a boolean array called "blnCallType(1 to 20)". I'll also create an boolean array called "blnMetStandard(1 to 2))".

If blnCallType(1) is true (there were calls of that type), and if they met the standard, I'll set blnMetStandard(1) as True and so on.

Assuming all blnMetStandards are true, I'll have Access regrade the employee as "Exceeds."

Thanks for your advice... it helped me change the way I'm thinking. If I'm missing something or overly complicating it in what I wrote above, please let me know. Otherwise, I'll probably be writing it Friday or Monday.

Thanks,
Paul

 

[PRMiller]

Hey Michael,

Revised my approach. I greatly reduced the amount of code I'm working with. I've got just about everything handled here, but I can't seem to find the proper way to use the basMaxVal function. After I call it towards the end, I'll set up another If/Then statement to evaluate for "Meets."

I would like basMaxVal to return the largest number of calls. I'll then match that call number up to the appropriate bucket number, check the bucket goal, and match bucket AHT against bucket goal to determine if the agent is meeting expectations. I'll insert this where I've made comments at the end.

Sub Productivity()

    Dim i As Integer
    Dim intBucketActive As Integer
    Dim intBucketAHT(1 To 16) As Integer
    Dim intBucketGoal(1 To 16) As Integer
    Dim intBucketMeets(1 To 16) As Integer
    Dim intProdRating As Integer
    Dim lngBucketCalls As Long
    Dim lngTotalCalls As Long

    For i = 1 To 16
        intBucketMeets(i) = 0
    Next i

    intBucketGoal(1) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 1"))
    intBucketGoal(2) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 2"))
    intBucketGoal(3) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 3"))
    intBucketGoal(4) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 4"))
    intBucketGoal(5) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 5"))
    intBucketGoal(6) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 6"))
    intBucketGoal(7) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 7"))
    intBucketGoal(8) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 8"))
    intBucketGoal(9) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 9"))
    intBucketGoal(10) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 10"))
    intBucketGoal(11) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 11"))
    intBucketGoal(12) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 12"))
    intBucketGoal(13) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 13"))
    intBucketGoal(14) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 14"))
    intBucketGoal(15) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 15"))
    intBucketGoal(16) = Nz(DLookup("AHTGoal", "tblPropertiesBuckets", "BucketNumber = 16"))
    lngTotalCalls = Nz(Forms!frmPCAScorecard("txtTotalCalls"))

    For i = 1 To 16
        If Forms!frmPCAScorecard("txtProdBucket" & i & "Calls").Value = 0 Then
            Forms!frmPCAScorecard("chkIncludeBucket" & i).Value = 0
        Else
            If Not IsNull(DLookup("BucketName", "tblPropertiesBuckets", "BucketNumber = " & i & "")) Then
                Forms!frmPCAScorecard("lblProdBucket" & i).Visible = True
                Forms!frmPCAScorecard("lblProdBucket" & i).Caption = DLookup("BucketName", "tblPropertiesBuckets", "BucketNumber = " & i & "")
            End If
            
            intBucketActive = intBucketActive + 1
            intBucketAHT(i) = Forms!frmPCAScorecard("txtProdBucket" & i & "AHT")
            lngBucketCalls = Forms!frmPCAScorecard("txtProdBucket" & i & "Calls").Visible
        
            Forms!frmPCAScorecard("chkIncludeBucket" & i).Value = -1
            Forms!frmPCAScorecard("txtProdBucket" & i & "Calls").Visible = True
            Forms!frmPCAScorecard("txtProdBucket" & i & "Min").Visible = True
            Forms!frmPCAScorecard("txtProdBucket" & i & "Min").Value = Format((Forms!frmPCAScorecard("txtProdBucket" & i & "AHT").Value / 24 / 60 / 60), "n:ss")
            Forms!frmPCAScorecard("txtProdBucket" & i & "Pct").Visible = True
            Forms!frmPCAScorecard("txtProdBucket" & i & "Pct") = lngBucketCalls / lngTotalCalls
            
            If intBucketAHT(i) <= intBucketGoal(i) Then
                intBucketMeets(i) = 1
            Else
                intBucketMeets(i) = 0
            End If
        End If
    Next i
    
    For i = 1 To 16
        intProdRating = intProdRating + intBucketMeets(1)
    Next i
    
    Call basMaxVal(lngBucketCalls)
    
    If intBucketActive = intProdRating Then
        Me.chkProdOverallExceeds.Value = -1
        Me.chkProdOverallMeets.Value = 0
        Me.chkProdOverallNeeds.Value = 0
    Else
        '######
        ' Determine if AHT was at least met in largest call type.  If so,
        ' Return "Meets" value
        '######
    End If
    
End Sub

Thanks again for all of your help -- I'm almost there!

Paul

 

[PRMiller]

Ok, got it solved. I added this last little bit of code to the end (replacing the existing If/Then statements deal):

    If intBucketActive = intProdRating Then
        Me.chkProdOverallExceeds.Value = -1
        Me.chkProdOverallMeets.Value = 0
        Me.chkProdOverallNeeds.Value = 0
    Else
        Set rs = New ADODB.Recordset
        
        With rs.Fields
            .Append "BucketNum", adInteger
            .Append "BucketAHT", adInteger, 10, adFldIsNullable
        End With
        
        varArray = Array("BucketNum", "BucketAHT")
        
        With rs
            .Open
            For i = 1 To 16
                .AddNew varArray, Array(i, (Forms!frmPCAScorecard("txtProdBucket" & i & "AHT")))
            Next i
        End With
        
        rs.Sort = "BucketAHT DESC"
        rs.MoveFirst
        intLgAHT = rs.Fields("BucketAHT")
        intLgBucket = rs.Fields("BucketNum")
        rs.Close
        Set rs = Nothing
        
        If intLgAHT <= intBucketGoal(intLgBucket) Then
            Me.chkProdOverallExceeds.Value = 0
            Me.chkProdOverallMeets.Value = -1
            Me.chkProdOverallNeeds.Value = 0
        Else
            Me.chkProdOverallExceeds.Value = 0
            Me.chkProdOverallMeets.Value = 0
            Me.chkProdOverallNeeds.Value = -1
        End If
    End If

Thanks for all of your help!
Paul