By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Calculating Averages

rdscott
P: 2
Access 2003

I have several fields in a table that I want to sum, get an average and update the avg value in another field. This is for each record. If anyone can help, what is the best way of doing this? I tried using the avg function, but it averages the whole column.
Jan 8 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 5K+
P: 8,434
I have several fields in a table that I want to sum, get an average and update the avg value in another field. This is for each record. If anyone can help, what is the best way of doing this? I tried using the avg function, but it averages the whole column.
At the simplest level, you could do an update query to update FieldX to ([Field1]+[Field2]+[Field3]/3).
Jan 8 '07 #2

rdscott
P: 2
thanks, but how would I eliminate the fields that have zero values. e.g. in your example if field 3 has a zero value, i want just field 1 and field 2 values divided by 2
Jan 8 '07 #3

Expert 5K+
P: 8,434
thanks, but how would I eliminate the fields that have zero values. e.g. in your example if field 3 has a zero value, i want just field 1 and field 2 values divided by 2
Perhaps it would be simplest to create a VB function which does the calculation, then use that function in your SQL.

For example (I forget how to write an UPDATE query)...
Expand|Select|Wrap|Line Numbers
  1. SELECT fAverageOf3([Fld1], [Fld2], [Fld3]) As FieldX FROM [TABLE];
Then in a VBA module, create a public function something like this (again, this is just off the top of my head)...
Expand|Select|Wrap|Line Numbers
  1. Public Function fAverageOf3(ByVal Val1 As Single, ByVal Val2 As Single, ByVal Val3 As Single) As Single
  2. Dim Count As Long
  3. Count = 0 - (Val1 <> 0) - (Val2 <> 0) - (Val3 <> 0)
  4. If Count Then
  5.   fAverageOf3 = (Val1 + Val2 + Val3) / Count
  6. End If
  7. End Function
Note that True is represented by -1, hence the subtractions in calculating Count.

I expect there are ways this approach could be made more generally applicable, rather than depending on a set type and number of fields (3 Single in this example).
Jan 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.