Hi!

I created an online survey that colects info from students. The radio
buttons submit a response from 1-5. I can create an ASP page with a
repeating region that displays the form online so the instructors can see a
FULL REPORT.

I would like to create an ASP page that somehow displays the average for each question (14 of them). I would then create a repeating region so the results are repeated for each day the class is taught.

I know what I want, I just can't figure out the ASP code required for the averaging variable.

I would also like to mention that I do not really know SQL.

Help <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks,
Mitch


When in doubt...reboot!

Your SQL statement should look like this:

SELECT Question, Avg(Response)
FROM Questions
GROUP BY Question

You can create this by opening new recordset and typing this statement in SQL field. Just change data to mach those from your table. If you still have problems send here name of table and fields you're using.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi!

I created an online survey that colects info from students. The radio
buttons submit a response from 1-5. I can create an ASP page with a
repeating region that displays the form online so the instructors can see a
FULL REPORT.

I would like to create an ASP page that somehow displays the average for each question (14 of them). I would then create a repeating region so the results are repeated for each day the class is taught.

I know what I want, I just can't figure out the ASP code required for the averaging variable.

I would also like to mention that I do not really know SQL.

Help <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks,
Mitch


When in doubt...reboot!
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

vfarcic,

This is what ULTRADEV has created for me:

SELECT *
FROM dbo.ostid_class_survey1
WHERE course_instructor = 'MMColParam'
ORDER BY course_date DESC

MMCOLPARAM is used to only find one particular instructor.

The SELECT * is being used becuse I am pulling a lot of information from the table, like names, dates, comments, department and so on.

The columns that contain answers are called Q_1 through Q_14.

The actual questions are hard coded on the page, not the best way of doing it, but it would take too long to explain why I did it that way...MANAGEMENT!!!

I will experiment with what you sent on MOnday, it's quiting time.

Thanks for offering help, I really appreciate it!!!


When in doubt...reboot!

SELECT Avg(called Q_1), Avg(called Q_2)...
FROM dbo.ostid_class_survey1
WHERE course_instructor = 'MMColParam'
GROUP BY Something
ORDER BY course_date DESC

Also, you can make View in SQL Server (or Query in Access) and make all calculations inside. After that just put that information on page. It's much easier to make SQL code from DB then from UD.


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
vfarcic,

This is what ULTRADEV has created for me:

SELECT *
FROM dbo.ostid_class_survey1
WHERE course_instructor = 'MMColParam'
ORDER BY course_date DESC

MMCOLPARAM is used to only find one particular instructor.

The SELECT * is being used becuse I am pulling a lot of information from the table, like names, dates, comments, department and so on.

The columns that contain answers are called Q_1 through Q_14.

The actual questions are hard coded on the page, not the best way of doing it, but it would take too long to explain why I did it that way...MANAGEMENT!!!

I will experiment with what you sent on MOnday, it's quiting time.

Thanks for offering help, I really appreciate it!!!


When in doubt...reboot!
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

vfarcic,

In you example code, the first line:

SELECT Avg(called Q_1), Avg(called Q_2)...

I am guessing that is were I would put ever field that I want to display on the ASP page, so it would be something like this (asuming there were only 4 questions):

SELECT Avg(called Q_1), Avg(called Q_2), Avg(called Q_3), Avg(called Q_4), course_date, course_instructor, course_date, student_name, student_department, student_email, ...

You used (called Q_#), does the word "called" automatically make a variable called Q_# ?

Do you recommend I use the "course_date" from above to group by? That way if the instructor had a bad day, the average would only effect that particular class and not all of them.

Thanks for your SUPER help, I look forward to trying it on Monday morning. I'll post my results and any other questions.

Thanks!!!!

Mitch

You can use AS:
SELECT Avg(called Q_1) AS AvgOfCalledQ1...
In this case variable is called 'AvgOfCalledQ1'. This way you can call variable however you want.

In any case you need to GROUP BY records 'couse there's no point in calculating AVG of one record. For each FecordField you put in SELECT line you need to use AVG or GROUP BY.

If your DB is in Acces you can send it to me and I'll make SQL statemt directly in Access. We can do the same for SQL Server.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
vfarcic,

In you example code, the first line:

SELECT Avg(called Q_1), Avg(called Q_2)...

I am guessing that is were I would put ever field that I want to display on the ASP page, so it would be something like this (asuming there were only 4 questions):

SELECT Avg(called Q_1), Avg(called Q_2), Avg(called Q_3), Avg(called Q_4), course_date, course_instructor, course_date, student_name, student_department, student_email, ...

You used (called Q_#), does the word "called" automatically make a variable called Q_# ?

Do you recommend I use the "course_date" from above to group by? That way if the instructor had a bad day, the average would only effect that particular class and not all of them.

Thanks for your SUPER help, I look forward to trying it on Monday morning. I'll post my results and any other questions.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I am almost 99% sure it is on a SQL server.

You said:

"there's no point in calculating AVG of one record"

YES! You are correct, that is why I thought it would be better to group by "course_date", and then filter out by using the "WHERE course_instructor = 'MMColParam'".

You are from Spain...tonight we are going to my favorite restaurant that serves a GREAT Paella!!

Thanks!

Mitch

It's much better to make View in SQL Server then to make SQL statement directly in UD. Try it and if you get stuck somewhere send the problem here.

P.S. I'm not from Spain. I'm born in Belgrade (Yugoslavia) and I'm living here in Barcelona or last 8 months. Paella is great together with many other 'spanish things'.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I am almost 99% sure it is on a SQL server.

You said:

"there's no point in calculating AVG of one record"

YES! You are correct, that is why I thought it would be better to group by "course_date", and then filter out by using the "WHERE course_instructor = 'MMColParam'".

You are from Spain...tonight we are going to my favorite restaurant that serves a GREAT Paella!!

Thanks!

Mitch





<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

vfarcic,

I have NO access to the SQL server, it took me a month to convince the Database Admin to create a table for me. I really don't think they will want to help me with this problem.

My department is trying to get it's own server, if that happens I will be able to do anything I want. I have a lot more experience with ACCESS than SQL.

Thanks!

Mitch

Edited by - mitchelt on 07/29/2001 00:27:53

vfarcic,

Here's the latest update.

I used the following code:

SELECT Avg(Q_1) AS avgq1, Avg(Q_2) AS avgq2, course_date
FROM dbo.ostid_class_survey1
WHERE course_instructor = 'MMColParam' GROUP BY course_date
ORDER BY course_date DESC

It looked liked it worked until I checked the results on my calculator and they were wrong.

The code returns the following averages:
Avg(Q_1) AS avgq1 = 2.00
Avg(Q_2) AS avgq2 = 2.00

These are the actual answers to the questions, if you do the math they don't match the above numbers:
Q_1: 1,3,2,5,1 Average is: 2.4
Q_2: 2,4,5,1,1 Average is: 2.6

Any idea why it is rounding or how to tell it to stop?

Thanks!

Mitch