Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculate the average of four fields in a query 1

Status
Not open for further replies.

dmillj

Technical User
Jan 29, 2007
9
US
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?
 
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
 
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
 
search these (Tek-Tips) fora with "basRowAvg" or look at thread701-764205.




MichaelRed


 
Here the UDF I've already posted:
Code:
'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
 
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.
 
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.
 
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
 
PVH,

Awsome! This works perfectly.

Thanks for your help!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top