Running Average calculation on the forms

[sindhuja]

Hi....

I have a form where we enter production records 1, production records 2, production records 3. We also enter other fields.... Just wondering if it's possible to create a calculated field on the form that would show what the production average for each production records for the past month is and a field that shows how the current entry affects that average. Basically, it would be nice if they could see a running average as they enter the production. I'm sure I can do that on a form.


-Sindhuja

[OBP]

Sindhuja, the easiest way to achieve your Average is to use a Query to calculate it and a very small Subform to display it on the Main Form. Just Requery the Subform each time a change is made to the values.

[sindhuja]

Thanks for the sugession.. let me give a try ….

But I need to compare the today’s entry with average of pastmonth.. [for ex todays average value is for the dates 7/29 to 6/29 then tomorrow the average values should be for 7/30 to 6/30] am not sure how get the date range………..

So for comparision if I have both the entries on the same form then that will be more helpful for me.
If the value I enter today is of much difference say + or – 20 of the average value then I need the todays textbox to be colored [something like alarm to the user]…

Have been struggling with this requirement for a quite long time….
any sugessions pls…

-sindhuja

[OBP]

You can set the date criteria in the Query, is it always 1 month or would 30 days do?
You can have both values on the form at the same time.
Can you provide some dummy data?

[sindhuja]

Hi..

Attached is the sample which am currently using… I will be filling the details as per the accounts.
Now I need to insert a textbox for each entry which will display the average.

Frmdetails - form with dummy textbox
Tbldetails – table in which I have entered dummy datas just for days. Now the challenge is to get the running date as criteria for finding the average…

Your help is highly appreciated…

-sindhuja

[OBP]

sindhuja, do you need averages of the fields S1 to S10?
I am concerned that those fields should be in a separate table as individual records.

[sindhuja]

No, I need average of S1 for the specific account say A1 in the date range for example 7/27/09 to 7/31/09 today and the date range should change tomorrow as 7/28/09 to 8/1/09 and goes on……

I have set up a table with all these fields together..

-Sindhuja

[OBP]

So the Account needs to be the same as the Account in the Record on the Form?
Will 30 days averaged out be OK as getting the Exact "Month's" worth of days in a Query is proving to be very difficult/

[sindhuja]

yes it should be same as in the form... and 30 days average will do..

[OBP]

Ok this form now has a subform showing the average of S1 for the last 3 days for each Account you move to in the main form records

[sindhuja]

Thanks a lot..

It seems to be confusion for the comparison purpose.
I want the average of S1 for the account A1 in the average box of S1.
This should be filled at the time of form open. Once I enter the count of S1 in todays box then It should compare with the average and act accordingly.

-Sindhuja

[OBP]

I had not added the Comparison, I wanted to make sure the Average was what you needed.

[OBP]

Try this, you can modify the Values and messages in the After Update Event procedure of the S1 field.

[sindhuja]

Hi OBP,

Am almost done with my requirement…
Now all I have to do is compare the todays value with the average of the account and change the color of todays textbox accordingly.

-Sindhuja

[OBP]

What Colours do you want?

[sindhuja]

Any color to differentiate between two text box...

-sindhuja