Okay, I have a survey form where coworkers rate each other. There are 9 

number fields where they can enter from 1-5, or 0 for n/a. I need to have 

Access compute an average of those fields that contain data (I know that 

Access ignores fields with 0 for averages, so that's good).



HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't 

work; text boxes evidently can only add, subtract, multiply & divide.



I've looked at the example codes for averages, but, as always, nothing 

fits my scenario! I need each survey averaged as it's completed, & then 

that average needs to go to a field on the table called ScoreAve, so that 

I can later pull a report to average the averages - it has to be done in 

that order; I can't take the average of all field1's, then field2's, etc, 

& average it that way (bummer!).



Thanks!



+Tammy

Tammy,



I think you'll have to do averaging the old fashioned way -- add up the

values in the fields and divide by the total number of fields.  



FieldWithAvg = (Field1 + field2+ field3)/3



--Susan



Susan B. Zeller

Office of Information Systems

College of Continuing Education

University of Minnesota

306 Wesbrook Hall

77 Pleasant Street SE

Minneapolis, MN 55455

Phone:   xxx-xxx-xxxx

Fax:   xxx-xxx-xxxx







-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Thursday, February 21, 2002 12:32 PM

To: Access

Subject: [access] Calculate a simple average!





Okay, I have a survey form where coworkers rate each other. There are 9 

number fields where they can enter from 1-5, or 0 for n/a. I need to have 

Access compute an average of those fields that contain data (I know that 

Access ignores fields with 0 for averages, so that's good).



HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't 

work; text boxes evidently can only add, subtract, multiply & divide.



I've looked at the example codes for averages, but, as always, nothing 

fits my scenario! I need each survey averaged as it's completed, & then 

that average needs to go to a field on the table called ScoreAve, so that 

I can later pull a report to average the averages - it has to be done in 

that order; I can't take the average of all field1's, then field2's, etc, 

& average it that way (bummer!).



Thanks!



+Tammy

That won't work, because the number of fields with a value will change 

from survey to survey, based on where a 0 is left (& should not count in 

the average). Thanks for the thought!



+Tammy



> Tammy,

> 

> I think you'll have to do averaging the old fashioned way -- add up the

> values in the fields and divide by the total number of fields.  

> 

> FieldWithAvg = (Field1 + field2+ field3)/3

> 

> --Susan

> 

> Susan B. Zeller

> Office of Information Systems

> College of Continuing Education

> University of Minnesota

> 306 Wesbrook Hall

> 77 Pleasant Street SE

> Minneapolis, MN 55455

> Phone:   xxx-xxx-xxxx

> Fax:   xxx-xxx-xxxx

> 

> 

> 

> -----Original Message-----

> From: Tammy Tappan [mailto:GRTappan@e...]

> Sent: Thursday, February 21, 2002 12:32 PM

> To: Access

> Subject: [access] Calculate a simple average!

> 

> 

> Okay, I have a survey form where coworkers rate each other. There are 9 

> number fields where they can enter from 1-5, or 0 for n/a. I need to 

have 

> Access compute an average of those fields that contain data (I know that 

> Access ignores fields with 0 for averages, so that's good).

> 

> HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't 

> work; text boxes evidently can only add, subtract, multiply & divide.

> 

> I've looked at the example codes for averages, but, as always, nothing 

> fits my scenario! I need each survey averaged as it's completed, & then 

> that average needs to go to a field on the table called ScoreAve, so 

that 

> I can later pull a report to average the averages - it has to be done in 

> that order; I can't take the average of all field1's, then field2's, 

etc, 

> & average it that way (bummer!).

> 

> Thanks!

> 

> +Tammy

Well, maybe I'm not too clear on what you are trying to do, but you can set

the default to 0 so that all will be at zero to begin with and then Access

would ignore what is not populated by the user b/c it is already 0. Just a

thought. 



--Susan





-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Thursday, February 21, 2002 2:20 PM

To: Access

Subject: [access] RE: Calculate a simple average!





That won't work, because the number of fields with a value will change 

from survey to survey, based on where a 0 is left (& should not count in 

the average). Thanks for the thought!



+Tammy



> Tammy,

> 

> I think you'll have to do averaging the old fashioned way -- add up the

> values in the fields and divide by the total number of fields.  

> 

> FieldWithAvg = (Field1 + field2+ field3)/3

> 

> --Susan

> 

> Susan B. Zeller

> Office of Information Systems

> College of Continuing Education

> University of Minnesota

> 306 Wesbrook Hall

> 77 Pleasant Street SE

> Minneapolis, MN 55455

> Phone:   xxx-xxx-xxxx

> Fax:   xxx-xxx-xxxx

> 

> 

> 

> -----Original Message-----

> From: Tammy Tappan [mailto:GRTappan@e...]

> Sent: Thursday, February 21, 2002 12:32 PM

> To: Access

> Subject: [access] Calculate a simple average!

> 

> 

> Okay, I have a survey form where coworkers rate each other. There are 9 

> number fields where they can enter from 1-5, or 0 for n/a. I need to 

have 

> Access compute an average of those fields that contain data (I know that 

> Access ignores fields with 0 for averages, so that's good).

> 

> HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't 

> work; text boxes evidently can only add, subtract, multiply & divide.

> 

> I've looked at the example codes for averages, but, as always, nothing 

> fits my scenario! I need each survey averaged as it's completed, & then 

> that average needs to go to a field on the table called ScoreAve, so 

that 

> I can later pull a report to average the averages - it has to be done in 

> that order; I can't take the average of all field1's, then field2's, 

etc, 

> & average it that way (bummer!).

> 

> Thanks!

> 

> +Tammy

Tammy,

    You can use the iif function to solve the problem:



    Total =  (Field1 + field2+ field3)

    FilledFields = iif(Field1=0,0,1) +iif(field2=0,0,1) +iif(field3=0,0,1)

    FieldWithAvg = Total/iif(FilledFields,0,1,FilledFields)



or you can do it all at once:



FieldWithAvg =  (Field1 + field2+ field3)/iif(iif(Field1=0,0,1)

+iif(field2=0,0,1) +iif(field3=0,0,1) )=0,1,iif(Field1=0,0,1)

+iif(field2=0,0,1) +iif(field3=0,0,1) )



The extra iif around the denominator is just in case all fields have 0 in

them you do not want to divide by 0.

The iif function can also be used in a query



Enjoy!



Barry



----- Original Message -----

From: "Tammy Tappan" <GRTappan@e...>

To: "Access" <access@p...>

Sent: Thursday, February 21, 2002 8:20 PM

Subject: [access] RE: Calculate a simple average!





> That won't work, because the number of fields with a value will change

> from survey to survey, based on where a 0 is left (& should not count in

> the average). Thanks for the thought!

>

> +Tammy

>

> > Tammy,

> >

> > I think you'll have to do averaging the old fashioned way -- add up the

> > values in the fields and divide by the total number of fields.

> >

> > FieldWithAvg = (Field1 + field2+ field3)/3

> >

> > --Susan

> >

> > Susan B. Zeller

> > Office of Information Systems

> > College of Continuing Education

> > University of Minnesota

> > 306 Wesbrook Hall

> > 77 Pleasant Street SE

> > Minneapolis, MN 55455

> > Phone:   xxx-xxx-xxxx

> > Fax:   xxx-xxx-xxxx

> >

> >

> >

> > -----Original Message-----

> > From: Tammy Tappan [mailto:GRTappan@e...]

> > Sent: Thursday, February 21, 2002 12:32 PM

> > To: Access

> > Subject: [access] Calculate a simple average!

> >

> >

> > Okay, I have a survey form where coworkers rate each other. There are 9

> > number fields where they can enter from 1-5, or 0 for n/a. I need to

> have

> > Access compute an average of those fields that contain data (I know that

> > Access ignores fields with 0 for averages, so that's good).

> >

> > HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't

> > work; text boxes evidently can only add, subtract, multiply & divide.

> >

> > I've looked at the example codes for averages, but, as always, nothing

> > fits my scenario! I need each survey averaged as it's completed, & then

> > that average needs to go to a field on the table called ScoreAve, so

> that

> > I can later pull a report to average the averages - it has to be done in

> > that order; I can't take the average of all field1's, then field2's,

> etc,

> > & average it that way (bummer!).

> >

> > Thanks!

> >

> > +Tammy




>




>

This is a multi-part message in MIME format.



------=_NextPart_000_0005_01C1BAF7.CC386160

Content-Type: text/plain;

	charset="US-ASCII"

Content-Transfer-Encoding: 7bit



Tammy,



There are a couple of ways of doing this.  You can add a button on your

form and after the user enters the data, click the button to peform the

calculations or you can create a funtion that returns the calculations

each time you update one of the survey fields.



Method 1:

I have five text boxes on a form they are called txtCalc1 thru txtCalc5

and a text box called txtAvg to store the averages.  I have a button

called cmdCalculateAve to perform the calculations.  Here is the code:



Private Sub cmdCalculateAve_Click()



    Dim intCount As Integer

   

    If CLng(txtCalc1) > 0 Then intCount = intCount + 1

    If CLng(txtCalc2) > 0 Then intCount = intCount + 1

    If CLng(txtCalc3) > 0 Then intCount = intCount + 1

    If CLng(txtCalc4) > 0 Then intCount = intCount + 1

    If CLng(txtCalc5) > 0 Then intCount = intCount + 1

   

   

    txtAvg = (CLng(txtCalc1) + CLng(txtCalc2) + CLng(txtCalc3) +

CLng(txtCalc4) + CLng(txtCalc5)) / intCount

   

End Sub



Method 2:

I have five text boxes on a form and they are called txtCalc1 thru

txtCalc5 and a text box called txtAvg to store the averages.  I have a

function the performs the calculation after each of the five text boxes

(txtCalc1 thru txtCalc2) are updated.  The function is called from each

text boxes' After Update event.  Here is the code



The Procedure code.



Private Function CalculateAvg() As Variant

   

    Dim intCount As Integer

   

    If CLng(txtCalc1) > 0 Then intCount = intCount + 1

    If CLng(txtCalc2) > 0 Then intCount = intCount + 1

    If CLng(txtCalc3) > 0 Then intCount = intCount + 1

    If CLng(txtCalc4) > 0 Then intCount = intCount + 1

    If CLng(txtCalc5) > 0 Then intCount = intCount + 1

   

   

    CalculateAvg = (CLng(txtCalc1) + CLng(txtCalc2) + CLng(txtCalc3) +

CLng(txtCalc4) + CLng(txtCalc5)) / intCount



End Function



Here is the code for calling the procedure.  (I'll only use the code

from the txtCalc1 text box as all the others are the same).





Private Sub txtCalc1_AfterUpdate()



   txtAvg = CalculateAvg()



End Sub



I personally stay away from Iif statements in VBA because I find they

can be confusing, especially if you must come back to the code a few

months from now and make changes.







John Ruff - The Eternal Optimist :-)



Always looking for Contract Opportunities



 



9306 Farwest Dr SW



Lakewood, WA 98498



papparuff@c...









-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Thursday, February 21, 2002 6:32 PM

To: Access

Subject: [access] Calculate a simple average!





Okay, I have a survey form where coworkers rate each other. There are 9

number fields where they can enter from 1-5, or 0 for n/a. I need to

have

Access compute an average of those fields that contain data (I know that

Access ignores fields with 0 for averages, so that's good).



HELP! Adding a text box & putting in Avg([field1]+[field2]etc) doesn't

work; text boxes evidently can only add, subtract, multiply & divide.



I've looked at the example codes for averages, but, as always, nothing

fits my scenario! I need each survey averaged as it's completed, & then

that average needs to go to a field on the table called ScoreAve, so

that

I can later pull a report to average the averages - it has to be done in

that order; I can't take the average of all field1's, then field2's,

etc,

& average it that way (bummer!).



Thanks!



+Tammy