Averaging different fields 3

[deante]

I have a table that holds information about different instructors and there ratings that were taken by a survey. I want to run queries that would give average scores for each person for all of its classes.
The table looks like this:

Instructor Course Rating 1 Rating 2 Rating 3
Joe Green Math 3 5 2
Brian White Reading 4 4 3
Mike Black Science 4 5 4
Joe Green Science 4 4 3

This database will constantly be updated and any instructor can teach any subject. If you need more information just ask. Thanks in Advance

 

[deante]

This is what the output field should be

Instructor Rating Average
Joe Green 3.50
Brian White 3.67
Mike Black 4.33

Thanks in advanced

 

[lespaul]

first write a query that normalizes your data:

SELECT Instructor, Course, Rating1 As Ratings FROM TableName
UNION
SELECT Instructor, Course, Rating2 FROM TableName
UNION
SELECT Instructor, Course, Rating3 FROM TableName


save this: qryNormal

Then query:

SELECT Instructor, Avg(Ratings) From qryNormal Group by Instructor


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[deante]

I tried that and I seem to be on the right track but what do I enter when it asks for parameter values. Whatever I put in seems to show up when I view the query not the information in my table.

Thanks in advance.

 

[lespaul]

can you post your two SQL queries please?

leslie

 

[deante]

Theres no need it took some trial and effort but I finally got the results I was looking for. I sent you a thank you already. Thanks for your help and expertise.

 

[lespaul]

glad you got it working! thanks for the star,

les

 

[willir]

Leslie - what a super solution!

 

[PHV]

For me the real normalising query should be:
SELECT Instructor, Course, Rating1 As Ratings FROM TableName
UNION ALL
SELECT Instructor, Course, Rating2 FROM TableName
UNION ALL
SELECT Instructor, Course, Rating3 FROM TableName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

waaaaaaaaaaaaaaaaay off the beaten track here. There are several "RowSet" aggregate functions previously posted here (in Tek-Tips). I believe there is at least one for the average, however it seems easier to re-create it than find it:

Public Function basAvgVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the AVERAGE or a series of values 5/25/05

    'Sample Usage:
    '? basAvgVal(1, 5, 9, 3, 13.663)
    ' 6.3326

    '? basAvgVal(1, 5, Null, 9, 3, Null, 13.663)
    ' 6.3326

    '? basAvgVal(9, 1, 5, 3, 13)
    ' 6.2

    Dim varAccum As Variant
    Dim Idx As Integer
    Dim intDenom As Integer

    While Idx <= UBound(varMyVals())
        If (Not IsNull(varMyVals(Idx))) Then
            If (IsNumeric(varMyVals(Idx))) Then
                varAccum = varAccum + varMyVals(Idx)
                intDenom = intDenom + 1
            End If
        End If
        Idx = Idx + 1
    Wend

    basAvgVal = varAccum / intDenom

End Function

Using the simple function can occassionally be useful. In this SQMPLE case, neither the SQL approach of the function approach would have any noticable impact on the operation (timing) of the process. For larger recordsets, there may easily be a difference (but I am not inclined to set up a verifiable test to show either is superior). On the other hand, the function use properly can be implemented with any number of "arguments" without resorting to the generation of a new query for variations in the number of numeric fields. Further, the function, while possibly introducing some distortion, will simply ignore any non-numeric fields, while the SQL approach will require the careful grooming of the recordset, or the introduction of an additional construct to exclude them. If the latter approach is taken, it would likely negate any advantage of the SQL solouotion (speed).





MichaelRed

 

[PHV]

Michael, your suggestion will calculate the overall average of the average of the 3 fields (instead of the overall average of the 3 fields), doesn't it ?

 

[MichaelRed]

PHV,

Depends on HOW you use it. The function itself just calculates *and hopefully returns) the average of the value list in the calling arg. Used as the UDF for a calculated field in a query, it would effectively be the RowSet aggregate for the field names supplied as the arg list, which (I believe) was the originally desired result and the point of the series of Union queries, to transform the Rows of Values to Columns so the Avg function could then be applied to the "transformed" data.

I believe that you are thinking of (considering?) using it (the UDF) in an assitional query on the results of your Union transformation and subsquent Aggregate query?

Is do, it is distinctlt NOT what I had in mind. Perhaps I am confusing the issue with all the verbiage?



MichaelRed