Tony Lennard wrote:
I have several queries, which generate about 10 fields each a text
field of length 2 (which contain effort and attainment grades), eg A2,
B4, A3, A3. I am trying to calculate an 11th fields, which is an
average of the other fields (in this case A3). Does anyone have any
ideas?
Tony Lennard
I might create another query. Drop in the query you created and drag
all fields or the * to it.
Create another column. Enter something like
AvgOfThe10 : GetAvg([Col1], [Col2]...[Col10])
This will call the function, contained in a module, called GetAvg.
Now, I suppose an A is greater than a B and so on. In the function, you
could get the left value. Let's assign A=5, B = 4...F = 1.
Now add the left value times 10 plus the right value
A3 = 5 * 10 + 3
53
B4 = 4 * 10 + 4
44
So now you add up all of the numbers and divide by 10 to get the average.
Let's say you had all 9 A1's and 1 A2. The number you would get would
be 583. Ex (59*9) + (52*1)
The average would be 58.
Now you calc for the letter. Int(58/10) = 5. So you determine that is
an A.
You can subract 58-(5*10) to get 8. Thus the average is A8.
Public Function GetAvg(v1, v2, v3....v10) As String
Dim intNumber As Integer
Dim intAvg As Integer
Dim intRemain As Integer
Dim intLetter As Integer
'get the total
intNumber = intNumber + (ParseValue(v1)*10) + Cint(Right(V1,1))
intNumber = intNumber + (ParseValue(v2)*10) + Cint(Right(V2,1))
intNumber = intNumber + (ParseValue(v3)*10) + Cint(Right(V3,1))
intNumber = intNumber + (ParseValue(v4)*10) + Cint(Right(V4,1))
...
'583 using the example above
intNumber = intNumber+(ParseValue(v10)*10) + Cint(Right(V10,1))
'get the average...58
intAvg = Int(intNumber / 10)
'we have 10 items we are avging and we multiplied by 10
'the letter so...83
intRemain = intNumber Mod 100
'get the letter...A
GetAvg = ParseAlpha(intAvg)
'note...Cint does rounding.
'Cint(80..85/10) = 8
'Cint(86..89/10) = 9
GetAvg = GetAvg & CInt(intRemain/10)
'this will return A8 to the column in the query
End Function
Public Function ParseValue(var)
Select case Left(var,1)
Case "A"
ParseValue = 5
Case "B"
ParseValue = 4
Case "C"
ParseValue = 3
Case "D"
ParseValue = 2
case else
ParseValue = 1
end select
End Function
Public Function ParseAlpha(var)
'we divide by 10 because A = 5*10 = 50
Select case Int(var/10)
Case 5
ParseAlpha = "A"
Case 4
ParseAlpha = "B"
Case 3
ParseAlpha = "C"
Case 2
ParseAlpha = "D"
case else
ParseAlpha = "E"
end select
End Function
