How to get a Running Average from a Combo Box

[SpiritedAway]

Hi,

I have a form, in which there are two drop down combo boxs.

comboStatus comboAverage
Draft In-Progress 10%
Draft Complete 15%
Draft Review 20%
Update In-Progress 30%
Update Complete 35%
Approval In-Progress 40%
Approved 50%
Execution In-Progress 60%
Execution complete 70%
Review In-Progress 75%
Interim approval 80%
Final approval 90%
Archive 100%


1) If I pick a status from comboStatus can I have comboAverage automatically update with the corresponding percentage value?

2) There will be about 50 documents in total, each one its own recordset, so 50 records - on the form how do I have a running average. An unbound text box that calculates all the percentages from comboAverage from each record and displays a running average.

comboStatus > comboAverage > RunningAverage

Thanks in advance for your help

 

[MrsGorilla]

For starters, you have comboAverage listed as your second combo box, but if I'm reading your post correctly, it's really not the average but the percentage of completion based on what is chosen in the status combo box. Based on that assumption, if you are going to have preset values for the combo box listed as you do below, then it's easier, in my opinion, to have a small lookup table for those values, as follows:

tblProgress

Status PercentComplete
Draft-In-Progress 10
Draft Complete 20

And so on...

Then, you have your comboStatus rowsource set to Table/Query, with the SQL statement as SELECT tblProgress.Status FROM tblProgress. You don't really need a combo box for the percentage if you want it to automatically populate based on what's chosen for status. You can just use a text box, then in the OnChange event for your status combo box you would put the following code:

Me.txtPercent.Value = DLookup("PercentComplete", "tblProgress", "Status = " & Me.comboStatus.Value)

This should update your text box automatically when you change the status in your combo box.

For the running average, it would be something similar, using an unbound text box as you said, and setting the control source equal to a DAvg function that polls directly from your recordset (a table?) to get the average from the 50 rows.

Hope this helps.

 

[SpiritedAway]

Thanks MrsGorilla for the response,

Regards the combo box, I will definitely try what you suggest…

As for the running average, you mention setting the control source in an unbound text box equal to a DAvg function that polls directly from my recordset.

So from tblProgress, if I want the running average of all 50 rows from PercentageComplete, column to be displayed in my unbound text box in my form could you specify the DAvg expression I would need and where it would go.

Thanks again for your help in this.