Avg of multiple fields?

[Smack]

Hey ya'll,
I have a form for a customer survey quistionnaire. There are twelve questions relating to our company and best competitor. These are numeric fields to do a ranking of 1-5. I need to know how to have a field calculate a running average for each column of 12. Not all questions will be asked at once, so this week 5 may have data, next week 2 more etc.
Hope this makes sense.

 

[SeeThru]

How about this.....

(this is junk code by the way - not tested!)

dim i, numQ, total, avg as integer
dim question as string 'make sure question field names are Question1 Question2 etc)

numQ=0
total=0

for i = 1 to 12
question="question" & i
if not isnull(me(question)) then
numq=numq+1
total=total+me(question)
end if
next i

avg=total/numq

me!Average=avg


Insert the code into the on_click of a button, the update of the last field, or create a function and run it on the update of every field for a running tally.

 

[Smack]

Bump

 

[jdegeorge]

Smack:

"Bump"? Are you hitting your head against a wall?

Code is your only answer. Acess doesn't have a built-in feature for what you're looking to do. Sorry.

If you post your table names, field names, and other information, maybe someone can help point you in the right direction. But, you won't get anyone to code it for you (a.k.a., doing your homework) because that's not what the site's about.

Try picking up a book on Access coding. That's how I got started, lo so many years ago.

Jim

"Get it right the first time, that's the main thing..."

 

[Smack]

Thank you for the response. "Bump" is just a way to bring a post back up from the dark chasms of oblivian.
I am not asking for any one to "do my homework" which is why I stated I do not know code. Although I have not been here much recently, I have spent a lot of time on this forum over the years and received invaluable input. Typically if I am told that it can only be done in code, I pass on the request if possible. I am self taught at hacking up access, and since my primary responsibility is quality systems manager for ISO, I typically do not have time to learn code, although it would be nice.
Again, thank you for your help.

 

[jdegeorge]

Smack

Sorry if I came off as preaching.

If you post your table names, field names, and other information, maybe I can help with the code.

Something like

Question Rating
1 2
2 3
3 1
4 4

etc.

Jim

"Get it right the first time, that's the main thing..."

 

[SpandexBobcat]

Sorry to resurrect this post but I was wondering if anyone could help me out.

I am using the code supplied here to calculate the average of 6 fields on a form. The problem is I can't for the life of me get it to include any decimal places, thus not giving an accurate enough answer. I would like it to go to 2 dp.

I have tried setting it in the table properties (decimal places, format) and in the form text field properties but it has no effect.

Here is my code:

Private Sub Command67_Click()

Dim i, numQ, total, avg As Integer
Dim velocity As String

numQ = 0
total = 0

For i = 1 To 6
   velocity = "velocity" & i
   If Not IsNull(Me(velocity)) Then
      numQ = numQ + 1
      total = total + Me(velocity)
   End If
Next i

avg = total / numQ

Me!AveReading = avg
End Sub

Could anyone give me some advice?

Thanks in advance.

Simon

"Windows: Just another pane in the glass.

 

[RoyVidar]

The only variable designated for numeric purposes you have declared explicitly, is avg (which is also a reserved word, I think, perhaps only in SQL, though), which is declared as integer - try single or double.

Roy-Vidar

 

[SpandexBobcat]

Hi Roy-Vidar,

Sorry to sound dumb but could you elaborate please.....

What exactly do you mean by 'try single or double'?

Thanks,

Simon

"Windows: Just another pane in the glass.

 

[DJN]

If you are trying to average a set of fields, e.g. average[fld1],[Fld2] etc.etc, you could use this function.

Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

 

[RoyVidar]

Integer stores values like this 1, 2, 50 000... - whole numbers, no fractions/decimals. Datatypes Single or Double, have the ability of also being able to store decimals/fractions.

[tt]dim intT as integer
dim sngT as single

intT = 4/3
sngT = 4/3
msgbox "Integer " & intT & " Single " & sngT[/tt]

Roy-Vidar

 

[SpandexBobcat]

Thanks for help guys.

I changed the field sizes to Double and set my 2dp. Then in the code used Dim i, numQ, total, avg As Single.

Not sure if it is overkill or it's exactly as it should be done but it works like a treat!

Simon

"Windows: Just another pane in the glass.