Average multiple column fields in access

Question

I have several fields in a query that I need to average. I can't calculate the average (Field1 + Field2 + Field3 )/3 won't
work. (Access 2019 version)

for example i have three value (6.7 + 6.7 + 6.7) with average 6.7 ،but in access result of below code

Private Sub Command13_Click()

myAvearge = ([col1] + [col2] + [col3]) / 3

End Sub

result is : 6.699999

so for (6.3 + 6.3 + 6.3) is : 6.300001

please help me, thank you

Answer 1

This is a FAQ.

It is completely normal since binary computers can only approximate floating point values.

You need to apply rounding if you want for example 2 decimals. See the Round function.

Answer 2

Either format the myAverage text box as Fixed with 1 or 2 decimal places, or change the code to

Me.myAverage = Round(Me.Col1 + Me.Col2 + Me.Col3) /3, 2)

Warning: if some of the text boxes Col1 to Col3 could be empty, this will return an incorrect result.

Answer 3

Hi. I would like to address the possibility of a bad table structure. Why are you averaging across columns rather than through rows?

I could be wrong, but the sample data you showed us could be a sign of a repeating group, which is against normalization rules.

Just a thought...

Answer 4

hi thanks

for example i have three measurement point of temperature that need to be averaged.

col1 : t1

col2 : t2

col3 : t3

Answer 5

hi thanks

for example i have three measurement point of temperature that need to be averaged.

col1 : t1

col2 : t2

col3 : t3

Hi. Thanks for the explanation. But as was already mentioned, what if you are missing any of the three measurements? Or, what if you needed more?