So far in this series, I've discussed the problem with querying textual or Boolean (Yes/No) data from repeated columns. But numeric data offers new challenges because we often want to do math on them. The most common kind of math is aggregation, that is, summing, counting, and averaging numeric values. This time, I'll talk about averaging data in repeated columns.
Others in this series:
- Multiple ORs
- Multiple Unions
- Multiple Joins
- Multiple Ifs
- Impossible Joins
- Summing
- Counting
- Averaging (this post)
Averaging Across Columns
The most common type of data aggregation, perhaps, is in calculating an Average where you divide the sum of the values by the count of the values.
In Excel, there is an AVERAGE function which will average the cells that have a value. The Excel function will work for any range of cells, across or down. In Access, however, the Average() function only works down columns, not across columns. So, averaging values in repeated rows is easy (see below), but just like summing and counting, averaging across repeated columns is more challenging.
For instance, suppose I had a table of student test scores:
If I wanted to average the test values for each student, I need to create an expression that sums the values for the numerator and counts the values for the denominator. For details on how to sum the values, see Aggregating Across Repeated Columns: Summing. To count the values, see Aggregating Across Repeated Columns: Counting
To do this, I need 4 stages.
1. Numerator: Sum the Values
Nz([Test1])+Nz([Test2])+Nz([Test3])+Nz([Test4]) AS TestSum
2. Denominator: Count the Values
Abs((Not IsNull([Test1])) + (Not IsNull([Test2])) + (Not IsNull([Test3])) + (Not IsNull([Test4])))
3. Handle Denominator of Zero
If the denominator is 0 (zero) the calculation will return the #Num! error. Therefore, I have to test the denominator for zero and convert it to a NULL. Dividing any value with a NULL will return NULL.
IIf((Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4]))))=0,Null,(Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4]))))
4. Average: Divide Numerator (Sum) by Denominator (Count)
(Nz([Test1])+Nz([Test2])+Nz([Test3])+Nz([Test4]))/IIf((Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4]))))=0,Null,(Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4])))))AS TestAverage
The full query would look like this:
SELECT Student, (Nz([Test1])+Nz([Test2])+Nz([Test3])+Nz([Test4]))/IIf((Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4]))))=0,Null,(Abs((Not IsNull([Test1]))+(Not IsNull([Test2]))+(Not IsNull([Test3]))+(Not IsNull([Test4]))))) AS TestAverage
FROM StudentScores_RepeatedColumns
ORDER BY Student;
Or in the Query Builder:
The result would look like this:
Averaging Down Rows
By contrast, suppose I normalize the table to remove the repeated columns. The table should look something like this:
Since the table is normalized (that is, the values go down a row), I can use the aggregate (or "Totals") functions that are built in to SQL. In this case, it's the Avg() function.
Avg(Score) AS TestAverage
The full query would look like this:
SELECT StudentID, Avg(Score) AS TestAverage
FROM StudentScores_Rows
GROUP BY StudentID
ORDER BY StudentID;
Or in the Query Builder:
Once again, the results of the queries are identical:
Now, with only 4 test scores, the expression to average repeated columns is manageable. But what if there were 20 or 50? The expression quickly becomes long and cumbersome. But with the normalized structure, the query doesn't change no matter how many test values there are.
Alternate Solution: User Defined Function (UDF)
Although the best solution is to normalize your database, it is often not practical with an existing database. In that case, a User Defined Function (UDF) may be a solution.
Fellow MVP, John Spencer has created a UDF for averaging any number of columns:
Public Function fRowAverage(ParamArray Values())
'John Spencer
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next I
If intElementCount > 0 Then 'At least one number in the group of values
fRowAverage = dblSum / intElementCount
Else 'No number in the group of values
fRowAverage = Null
End If
End Function
The function should go in a General Module. Then it can be used in a query as follows:
SELECT
Student,
fRowAverage([Test1],[test2],[Test3],[Test4]) AS TestAverage
FROM StudentScores_RepeatedColumns
ORDER BY Student;
No comments:
Post a Comment