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