Average across multiple fields?

Jay

unread,

May 12, 2003, 5:43:01 PM5/12/03

to

Hope someone can help! I need to average numeric values (and ignore
blank values) across 10 fields per record. I've read similar posts
where the advice is "normalize the data so all the numbers are in one
field" - BUT - this data will be entered by multiple users via a form.
They need to be able to easily navigate all 10 fields (and other text
fields) onscreen - and I need to get an overall average across the 10
fields to appear on a report. With the multiple different reports I
need, Excel doesn't seem like an option at all. Is there a way to do
this?

Bullschmidt

unread,

May 12, 2003, 9:11:49 PM5/12/03

to

Jay,

Perhaps something like the following in a module would be helpful:

Public Function AverageIt(pvarFld1 As Variant, pvarFld2 As Variant,
pvarFld3 As Variant, pvarFld4 As Variant, pvarFld5 As Variant,
pvarFld6 As Variant, pvarFld7 As Variant, pvarFld8 As Variant,
pvarFld9 As Variant, pvarFld10 As Variant) As Double
On Error GoTo Err_AverageIt

' Init.
intCount = 0

If Not IsNull(pvarFld1) Then intCount = intCount + 1
If Not IsNull(pvarFld2) Then intCount = intCount + 1
If Not IsNull(pvarFld3) Then intCount = intCount + 1
If Not IsNull(pvarFld4) Then intCount = intCount + 1
If Not IsNull(pvarFld5) Then intCount = intCount + 1
If Not IsNull(pvarFld6) Then intCount = intCount + 1
If Not IsNull(pvarFld7) Then intCount = intCount + 1
If Not IsNull(pvarFld8) Then intCount = intCount + 1
If Not IsNull(pvarFld9) Then intCount = intCount + 1
If Not IsNull(pvarFld10) Then intCount = intCount + 1

If intCount > 0 Then
AverageIt = (Nz(pvarFld1) + Nz(pvarFld2) + Nz(pvarFld3) +
Nz(pvarFld4) + Nz(pvarFld5) + Nz(pvarFld6) + Nz(pvarFld7) +
Nz(pvarFld8) + Nz(pvarFld9) + Nz(pvarFld10)) / intCount
Else
AverageIt = 0
End If

Exit Function

Err_AverageIt:
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) &
Err.Description & ".", vbExclamation
Exit Function
End Function

Best regards,
J. Paul Schmidt - Freelance ASP Web Developer
http://www.Bullschmidt.com - Creating "dynamic" Web pages that read
and write from databases...

jaym...@aol.com (Jay) wrote in message news:<a08153f2.03051...@posting.google.com>...

Andy Hilliard

unread,

May 13, 2003, 3:25:36 AM5/13/03

to

IMHO, whenever you find yourself saying "We should normalize this
data, but..." the answer is to stop right there. The "but" is probably
some sort of rationalization for a quick-and-dirty approach. Remember,
the dirty will be apparent long after the quick has been forgotten.

You can definitely have 10 records which a user accesses via a form.
It takes a little bit of programming behind the form, but not a lot.

What you are describing is something that SQL was built to do. You
want to run an AVG aggregate function across 1 to n records. If the
record is blank, you want to ignore it.

Let's assume that the blank value is a null. This SQL returns the
result you want, and is maintainable, easy to understand, and will run
fast.

SELECT AVG(myField)
FROM myTable
WHERE myField Is Not Null

Regards-
Andy Hilliard
www.organonweb.com
andyh@

pa...@bullschmidt.com (Bullschmidt) wrote in message news:<3b1adcec.03051...@posting.google.com>...