Calculating Row Average

[Matt]

I'm trying to calculate a row average in a table with 51 months. I created
an Avg function that I call in an update query.

It is called RAvg and the code is listed below. However, when I try to
calculate the average across 51 months, Access gives me the error message,
"The expression you entered is too complex." My update query looks like
this:

RAvg([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51])

Is there any way to get around this? Thanks!


Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

 

[John W. Vinson]

I'm trying to calculate a row average in a table with 51 months. I created
an Avg function that I call in an update query.

Is each month a FIELD!? Ouch!!! That's badly non-normal design. Is that
intentional?

It is called RAvg and the code is listed below. However, when I try to
calculate the average across 51 months, Access gives me the error message,
"The expression you entered is too complex." My update query looks like
this:

RAvg([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51])

Is there any way to get around this? Thanks!


Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

About all you'll be able to do is to rewrite RAvg() to open a Recordset
consisting of this single field and loop through its Field collection. Does
your record have a primary key, or is it altogether a spreadsheet design...?

John W. Vinson [MVP]

 

[Sergey Poberezovskiy]

Matt,

Though it may not be as fast as a query, but have you considered opening a
recordset and perform updates on the recordset in code?

Otherwise I think you will need to try to split your query in say a number
of queries. Something like 5 sub queries, each of which would return your
record key field(s) and both sum and count of the 10 fields in question (and
one with 11). Then you will manually create an average calculation in the
total query. Though it looks ugly, it may get you over the line.

Another option is to transpose your query manually, something similar to the
following:
select KeyFields, Avg(Value)
From
(
select KeyFields, Month1Value As Value
union all
select KeyFields, Month2Value
....
union all
select KeyFields, Month51Value
)

 

[Matt]

Any suggestions?

Any help is very appreciated!

Thanks,
Matt

 

[John Nurick]

Hi Matt,

As others have suggested, the preferred approach (when using relational
database software) is to normalise your data.

Other suggestions:

1) Work in Excel.

2) Revise your function to take a single string parameter, parse it and
return the value you want. Air code:

Public Function SAvg(V As Variant) As Variant
Dim arItems As Variant
Dim I as Variant
Dim NumItems As Long
Dim SumItems As Double

arItems = Split(V, " ")
For Each I in arItems
If IsNumeric(I) Then
NumItems = NumItems + 1
SumItems = SumItems + I
End If
Next

If NumItems <> 0 Then
SAvg = SumItems / NumItems
Else
SAvg = Null
End If
End Function

Any suggestions?

Any help is very appreciated!

Thanks,
Matt

I'm trying to calculate a row average in a table with 51 months. I created
an Avg function that I call in an update query.

It is called RAvg and the code is listed below. However, when I try to
calculate the average across 51 months, Access gives me the error message,
"The expression you entered is too complex." My update query looks like
this:

RAvg([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51])

Is there any way to get around this? Thanks!


Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

 

[missinglinq via AccessMonster.com]

I agree with John Nurick, this is what Excel was written for!

Hi Matt,

As others have suggested, the preferred approach (when using relational
database software) is to normalise your data.

Other suggestions:

1) Work in Excel.

2) Revise your function to take a single string parameter, parse it and
return the value you want. Air code:

Public Function SAvg(V As Variant) As Variant
Dim arItems As Variant
Dim I as Variant
Dim NumItems As Long
Dim SumItems As Double

arItems = Split(V, " ")
For Each I in arItems
If IsNumeric(I) Then
NumItems = NumItems + 1
SumItems = SumItems + I
End If
Next

If NumItems <> 0 Then
SAvg = SumItems / NumItems
Else
SAvg = Null
End If
End Function

Any suggestions?

[quoted text clipped - 36 lines]