Calculating an AVG Score

[dpye]

Before I start I should mention that I am only new to ASP scripts so I appologize if this seems like a simple question.

The site I am working with is a survey page where users can answer 1-5 for any of the 34 questions. The answers are then written back to an Access databse. This part works fine.

What I want to do now is calculate an AVG score for each question. I think I can use recordcount ot get the number of records but I'm not sure where to go from there. Does anyone have any suggetions on how I can accomplish this.

Thanks

 

[steven290]

try, in your sql

SELECT AVG(answer) FROM test WHERE question=1

 

[Sheco]

You can accomplish this with SQL and have the database do the hard work.

What is your table structure? Do you have a big table with one field for each question where each row represents an individual complete survey?

 

[ethorn10]

is each question its own field in the database? like

Question1 | Question2 | Question 3
    2     |     1     |     5
    4     |     2     |     4
    3     |     2     |     4
    3     |     3     |     3
    1     |     5     |     5
    5     |     4     |     2

If so, then just write an sql statement that averages the questions you want to display:

strsql = "select avg(Question1) from tblname group by Question1"

Or something similar anyway.

 

[ethorn10]

man you guys are quick...looks like i should spend less time drawing cute little tables and more time answering the question.

 

[PaulBricker]

Are you calculating the AVG for all the questions? I would do that directly in the database. Set up a totals query and get your averages, and then post those values back to your page. Unless I misunderstanding something.


Paul

 

[Sheco]

The exact answer will depend on the table structure but I think your answer is more explanitory... the pictures do help.

 

[PaulBricker]

ethorn10, there wasn't a response when I started my reply, and I'm further down the line than you.
Sheesh!!

Paul

 

[steven290]

lol

 

[Sheco]

A school of piranhas comes to mind.

 

[PaulBricker]

Hmmm! A school of slow piranhas.


Paul

 

[dpye]

Thanks for all the quick responses...I've been in meetings all day so I didn't check my mail until this evening. To answer Ethorn10 question, yes the tables is laid out as he displayed with fields of q1, q2 q3 etc. I will try your suggestions tomorrwo morning and let you know how it works out.

I was also thinking about having Access do the work, like was mentioned here, I just wasn't sure how to bring the results of the Query into the webpage.

 

[PaulBricker]

You would just open a recordset and display the data on the page something like this (this is just a portion of code I use so watch for missing syntax)

<%WHILE NOT rst.EOF %>
		
		<% 	
			

		FOR i = 0 to rst.fields.count-1
		
		%>
		<p>
	
		<%
		If i = 0 Then
		%><h2><%
		Response.Write Replace("<label><font size = 4 font color = #FF3300> '" & rst(i).Name & "':</label></font><font size = 4 font color = #0000CC> '" & rst(i) & "'</font>","'","")%></h2><%
		Else
		Response.Write Replace("<label><font size = 3> '" & rst(i).Name & "':</label><font color = #0000CC> '" & rst(i) & "'</font>","'","")
		End If
		'Response.Write "<td align=center valign=top bgcolor='#FFFFE5'>" & rst(i)& "</td>"
		NEXT 
		rst.MoveNext
	WEND

Paul