Storing Avg of three measurements

[Guest]

All,
I would like to have an input form that has several measurements
typed into it, but I want to store only the final average of these
measurements into a single field. We work in a laboratory setting where
several thickness are measured to obtain a final average thickness used for
density calculations. I will never need the individual measurements
stored.......just the final average. Any help would be greatly appreciated
! Thanks!

 

[Allen Browne]

You will need a form with 3 unbound text boxes (for entering the 3 original
values you don't need stored), and one text box bound to the field where the
average is stored.

In the AfterUpdate event procedure of the 3 text boxes, calculate the
average, and assign the result to your real field.

Something like this:

Private Sub Text0_AfterUpdate()
Dim dblHowMany As Double
Dim dblTotal As Double

If Not IsNull(Me.[Text0) Then
dblTotal = dblTotal + Me.[Text0]
dblHowMany = dblHowMany + 1.0
End If

If Not IsNull(Me.[Text1) Then
dblTotal = dblTotal + Me.[Text1]
dblHowMany = dblHowMany + 1.0
End If

If Not IsNull(Me.[Text1) Then
dblTotal = dblTotal + Me.[Text2]
dblHowMany = dblHowMany + 1.0
End If

If dblHowMany = 0.0 Then
Me.[MyRealFeld] = Null
Else
Me.[MyRealFeld] = dblTotal / dblHowMany
End If
End Sub

Private Sub Text1_AfterUpdate()
Call Text0_AfterUpdate
End Sub

Private Sub Text2_AfterUpdate()
Call Text0_AfterUpdate
End Sub

You will also want to reset these boxes in the form's Current and Undo event
procedures:

Private Sub Form_Current()
Me.Text0 = Null
Me.Text1 = Null
Me.Text2 = Null
End Sub

Private Sub Form_Undo()
Me.Text0 = Null
Me.Text1 = Null
Me.Text2 = Null
End Sub

 

[Larry Daugherty]

You haven't given sufficient information to permit a certain solution:
What's the maximum number of input variables? Are each of them always
present?

For just a few variables, create an unbound textbox control on your
form for each one. Create one textbox control based on the
FinalAverage field in the underlying table. Create a command button
that will sum the values in the individual unbound textboxes, divide
that sum by the count of controls with valid (you have to determine
what that is) entries and store that value as the FinalAverage.

For a large number of variables you'd probably be better off with a
child table to store the input values and to never store the
calculated value. My bias would be to do it that way. Input values
can be off due to typos. If the input values are tossed you can never
evaluate the individual inputs. Without knowing the world in which
your application serves I'd think there might be a value in being to
track a given parameter over time....

HTH

 

[Guest]

Thank You so Much !!!

....This will make my job so much easier. Thanks again !

You will need a form with 3 unbound text boxes (for entering the 3 original
values you don't need stored), and one text box bound to the field where the
average is stored.

In the AfterUpdate event procedure of the 3 text boxes, calculate the
average, and assign the result to your real field.

Something like this:

Private Sub Text0_AfterUpdate()
Dim dblHowMany As Double
Dim dblTotal As Double

If Not IsNull(Me.[Text0) Then
dblTotal = dblTotal + Me.[Text0]
dblHowMany = dblHowMany + 1.0
End If

If Not IsNull(Me.[Text1) Then
dblTotal = dblTotal + Me.[Text1]
dblHowMany = dblHowMany + 1.0
End If

If Not IsNull(Me.[Text1) Then
dblTotal = dblTotal + Me.[Text2]
dblHowMany = dblHowMany + 1.0
End If

If dblHowMany = 0.0 Then
Me.[MyRealFeld] = Null
Else
Me.[MyRealFeld] = dblTotal / dblHowMany
End If
End Sub

Private Sub Text1_AfterUpdate()
Call Text0_AfterUpdate
End Sub

Private Sub Text2_AfterUpdate()
Call Text0_AfterUpdate
End Sub

You will also want to reset these boxes in the form's Current and Undo event
procedures:

Private Sub Form_Current()
Me.Text0 = Null
Me.Text1 = Null
Me.Text2 = Null
End Sub

Private Sub Form_Undo()
Me.Text0 = Null
Me.Text1 = Null
Me.Text2 = Null
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

All,
I would like to have an input form that has several measurements
typed into it, but I want to store only the final average of these
measurements into a single field. We work in a laboratory setting where
several thickness are measured to obtain a final average thickness used
for
density calculations. I will never need the individual measurements
stored.......just the final average. Any help would be greatly
appreciated
! Thanks!