Displaying calculations on a form..

  • Thread starter Thread starter XYZ-Man
  • Start date Start date
X

XYZ-Man

I have a user that inputs real time data on a production line into a
form, form1. This simple form has two fields, time and count. Every 10
minutes or so, the user inputs time and count. Simple.

What I would like to do is to have another form (form2) open all the
time, which would average the last 5 entries of count entered in form1
and display that number.

(As the user input the next set of time and count in form1, form2
would automatically update itself with the new average).

Since I lack SQL skills, can someone help me explain how to do this in
beginner terms?

Thank you,
Robert
 
open a new query, go to SQL View, and paste in the following SQL statement.
make sure you change the field names (pCount and pTime) and table name
(tblProduction) to the correct names in your database. the table you need to
use is the same table your data entry form is bound to.

SELECT TOP 5 Avg(pCount) AS AvgProduction
FROM [SELECT TOP 5 pCount
FROM tblProduction
ORDER BY pTime DESC]. AS qryTopFive;

close and save the query, i'll call it qryProductionAvg. use this query to
create a new form, i'll call it frmProductionAvg; this is your "open all the
time" form.

in your data entry form, requery the other form from the AfterUpdate event,
as

Private Sub Form_AfterUpdate()

Forms!frmProductionAvg.Requery

End Sub

now, each time your user enters a time and count in the data entry form (or
changes time and/or count in an existing record), and then leaves the record
or closes the form, the average in the other form will automatically update.
if you're not familiar with VBA procedures, post back and i'll tell you how
to add the code to the data entry form's module.

hth
 
Back
Top