Query Help: Average of Columns Per Row

  • Thread starter Thread starter Idgarad
  • Start date Start date
I

Idgarad

I have a table:

DATE, Region, HOUR0, HOUR1, ... HOUR23
1/1/10,ABC, 123, 231,...,141


I want to write a query that returns not only the records filtered by
Region (That was easy with a parameter) but also the daily average
appended for importing into an Excel spreadsheet for charting purposes
(hence I need the daily average computed at run time):

DATE, Region, HOUR0, HOUR1,...,HOUR23,DAILYAVERAGE
1/1/10,ABC, 123, 231, ..., 141, 271

Any ideas?
 
Idgarad said:
I have a table:

DATE, Region, HOUR0, HOUR1, ... HOUR23
1/1/10,ABC, 123, 231,...,141


I want to write a query that returns not only the records filtered by
Region (That was easy with a parameter) but also the daily average
appended for importing into an Excel spreadsheet for charting purposes
(hence I need the daily average computed at run time):

DATE, Region, HOUR0, HOUR1,...,HOUR23,DAILYAVERAGE
1/1/10,ABC, 123, 231, ..., 141, 271

Any ideas?
Click to expand...

Idea 1
Redesign your database and make a table with Date, Region, Hour (0 thru
23), Value

Idea 2
Calculate the avarage as (HOUR0 + HOUR1 +.....HOUR23)/24
 
You can use a VBA function to calculate a row average. Here is one that I
wrote a while back. Life would be simpler if you had a normalized table
structure as noted in the first response to your posting.

Your call to it would be
fRowAverage(Hour0,Hour1,...,Hour23)

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'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

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top