If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Help: Average of Columns Per Row
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? |
#2
|
|||
|
|||
Query Help: Average of Columns Per Row
Idgarad wrote:
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? 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 -- Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
Query Help: Average of Columns Per Row
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 XPS35 wrote: Idgarad wrote: 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? 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 |
Thread Tools | |
Display Modes | |
|
|