Calculate the average of four fields in a query 1

[dmillj]

I have a query in which I am gathering measurement history. I have four fields named as follows: Result 1; Result 2; Result 3; Result 4.

What I am trying to do is add the four result fields together and divide by four to get the average. I know how to do this much, my problem lies when one or more of the fields does not contain data, the average does not calculate correctly. How do you create an expression that will allow an average to be calculated wether or not all four fields contain data?

 

[PHV]

Have a look at the Nz and IsNull functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

Additionally, read the fundamentals document linked below to see why your current structure isn't normalized. (Fields with names Something1, Something2, Something3 is a red flag that your tables aren't normalized)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MichaelRed]

search these (Tek-Tips) fora with "basRowAvg" or look at thread701-764205.




MichaelRed

 

[PHV]

Here the UDF I've already posted:

'A generic function to get the average value of an arbirtrary numbers of numeric values:
Public Function myAvg(ParamArray Args())
Dim i As Long, N As Long, rv
For i = 0 To UBound(Args)
  If IsNumeric(Args(i)) Then rv = rv + Args(i): N = N + 1
Next
myAvg = rv / N    ' N=0 <=> rv is null
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmillj]

Thanks everyone for your quick response and great suggestions.

PHV, I have a question regarding the code you posted since I am new to writing code. Am I able to use the code as is, or do I need to add in my fields that I want to calculate? If I need to add in my [result] fields, can you show me how that would look using your code?

Thanks again for the help.

 

[dmillj]

PHV,

To show my complete ignorance of writing code, where would I put the code that you posted? Does that get entered into the 'On Click' event for the average field in my results 'Form', or does it get entered into the query?

Thanks.

 

[PHV]

The function must be created in a standard code module.

Then in the query grid:
Expr1: myAvg([Result 1], [Result 2], [Result 3], [Result 4])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dmillj]

PVH,

Awsome! This works perfectly.

Thanks for your help!!