Prev: How do you execute an Access Pgm?
Next: Restrict a report to a given date range
From: Mike DFR on 16 Mar 2010 05:46
I have several rows of data in a field, I need to average all the entries in
one row
I have 12 fields for 12 months of data, I need the average of the sum of all
non blank entries.
For example 3 months completed, the solution in Excel is
(field1+field2+field3)/3
I am looking for method to average the sum in Access
From: John Spencer on 16 Mar 2010 08:43
One way if you can't change your data is to use a VBA function. I've posted
one below. You would call it in a calculated field in a query. Assuming your
field names are the abbreviated month names the expression might look like the
following.

Field: fRowAverage(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

====== Copy and paste the following into a VBA module and save. The module
must have a name other than fRowAverage
=========================================================================
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

Mike DFR wrote:
> I have several rows of data in a field, I need to average all the entries in
> one row
> I have 12 fields for 12 months of data, I need the average of the sum of all
> non blank entries.
> For example 3 months completed, the solution in Excel is
> (field1+field2+field3)/3
> I am looking for method to average the sum in Access
From: John W. Vinson on 16 Mar 2010 12:54
On Tue, 16 Mar 2010 02:46:01 -0700, Mike DFR
<MikeDFR(a)discussions.microsoft.com> wrote:

>I have several rows of data in a field, I need to average all the entries in
>one row
>I have 12 fields for 12 months of data, I need the average of the sum of all
>non blank entries.

Then you have an incorrectly designed table.

>For example 3 months completed, the solution in Excel is
>(field1+field2+field3)/3
>I am looking for method to average the sum in Access

Excel is a spreadsheet program, best of breed.
Access is a relational database development environment.
THEY ARE DIFFERENT!!!

Access is not "Excel on steroids"; it's a different program, with a different
design philosophy. Your table is a perfectly fine spreadsheet, but it's
completely inappropriate for a database - you're just finding out why!

What you ask can be done, but what you really should do is "Normalize" your
table. One big part of normalization is to get rid of repeating fields. Rather
than twelve *fields*, one for each month, a proper design would have twelve
*rows*, one amount for each, in a related table. If these are payments, you
would have a Payments table with a link to this table (I'm guessing it's a
table of accounts, or items paid for, or something of the sort), a PaymentDate
field (which you can use to identify the month), and an Amount field. You can
then do a very simple Totals query to average across any range of dates - a
full year, this year to date, or even the past twelve months (which will be
monstrously difficult in your current structure).

If you're going to use Access effectively, it's important to design your
tables to work with Access, rather than struggling against it! See:


Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
From: David W. Fenton on 16 Mar 2010 15:49
John Spencer <spencer(a)chpdm.edu> wrote in
news:en1ZFZQxKHA.4240(a)TK2MSFTNGP06.phx.gbl:

> One way if you can't change your data is to use a VBA function.
> I've posted one below. You would call it in a calculated field in
> a query. Assuming your field names are the abbreviated month
> names the expression might look like the following.
>
> Field:
> fRowAverage(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
>
>====== Copy and paste the following into a VBA module and save.
>The module
> must have a name other than fRowAverage

[code snipped]

Good function. I've added it to my collection of "immediate
functions", including iMax() and iMin(). I've renamed it iAve().

It also occured to me that if you didn't want to worry about passing
non-numeric values, you could do it without walking the array. The
code for that is after my sig.

Your version is more bulletproof, and for the size of array that is
the limit in a SQL statement, shouldn't be a performance issue. But
I thought it was fun to see what methods were available to total an
array of numbers. I do so love me my Split() and Join() functions!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function iAve(ParamArray Values()) As Double
Dim strSum As String
Dim dblSum As Double
Dim lngItemCount As Long

strSum = Join(Values(), "+")
dblSum = Eval(strSum)
lngItemCount = UBound(Values()) + 1
iAve = dblSum / lngItemCount
End Function
 | 
Pages: 1
Prev: How do you execute an Access Pgm?
Next: Restrict a report to a given date range