445,704 Members | 1,867 Online Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

# Calculating Averages

 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
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

 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 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 Public Function fAverageOf3(ByVal Val1 As Single, ByVal Val2 As Single, ByVal Val3 As Single) As Single Dim Count As Long Count = 0 - (Val1 <> 0) - (Val2 <> 0) - (Val3 <> 0) If Count Then   fAverageOf3 = (Val1 + Val2 + Val3) / Count End If 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 