hello,



   i could really use some help with this one...i have a form with 50 

questions. 40 of these questions have checkboxes with either "yes" or "no" 

as the answer. these yes and no answers also have a weighted value, i.e., 

whether you checked yes or no for, example, question 5, it has a value of 

2. same thing with any other question but it might have a different value.

(these values are stored in a seperate table in my database)



what has to happen after this form is submitted is that i need to find out 

how many questions are yes and how many are no. the code i already have 

puts the data correctly into my database. i have to total the point values 

for all the yes answers and total the point values for all the no answers 

then i have to take these figures and divide them to get my score.i need 

to do this from the data that i just put in the database rather than from 

looping through the actual form. 

 can somebody suggest or give an example of a way that i can do this? i'm 

kind of a newbie at this so any code examples would be really great 

use the count attribute





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

From: stephen.eighmey@s... [mailto:stephen.eighmey@s...]

Sent: Tuesday, February 26, 2002 5:51 PM

To: ASP Databases

Subject: [asp_databases] getting score help please...





hello,



   i could really use some help with this one...i have a form with 50 

questions. 40 of these questions have checkboxes with either "yes" or "no" 

as the answer. these yes and no answers also have a weighted value, i.e., 

whether you checked yes or no for, example, question 5, it has a value of 

2. same thing with any other question but it might have a different value.

(these values are stored in a seperate table in my database)



what has to happen after this form is submitted is that i need to find out 

how many questions are yes and how many are no. the code i already have 

puts the data correctly into my database. i have to total the point values 

for all the yes answers and total the point values for all the no answers 

then i have to take these figures and divide them to get my score.i need 

to do this from the data that i just put in the database rather than from 

looping through the actual form. 

 can somebody suggest or give an example of a way that i can do this? i'm 

kind of a newbie at this so any code examples would be really great 




$subst('Email.Unsub').

thank you. i looked into the count method and it won't completely solve my 

problem. the problem is that each value of either yes or no has a certain 

weight or sub-value to it, so that if i were to add up all of the yes and 

no answers, they wouldn't each have 1 added to them, they would have their 

particular point value added. what i'm trying right now is looping through 

the answers, if i get a yes, i connect to another database to grab the 

value of that particular "yes" answer, and then add them all together. 

needless to say i'm having some problems. any suggestion or examples? 

thanks again for your help.

here is the code i have so far...



<%



checkbox = Request.Form("checkbox")



set conn = Server.CreateObject("ADODB.Connection")

conn.open "CallCenter","callcenter","ccform"

for i = Q1 to Q34

myConn = "SELECT (i) FROM vsatwbt.callCenter WHERE strID = '"& TS_QAID &"'"

do while not rs.EOF

if rs("i") = "yes" then

myConnCheck = "SELECT * FROM vsatwbt.pointValue WHERE i = 'i'"

pointValueYes = pointValueYes + i

elseif rs("i") = "no" then

myConnCheck = "SELECT * FROM vsatwbt.pointValue WHERE i = 'i'"

pointValueNo = pointValueNo + i

end if

rs.movenext

loop

next







rs("newTotal") = (pointValueYes + pointValueNo)

rs("varScore") = (pointValueYes/newTotal)





%>

I assume you have a table like this:



TABLE Answers

QuestionID (FK references Questions table)

UserID         (FK key, references Users table)

Answer    (Yes/No field)



To get the number of yes and no answers, you do:



SELECT Answer, Count(Answer)

FROM Answers

WHERE UserID = 1

GROUP BY Answer



This would return two records, each record having 2 fields. The first field

is Yes or No, and the second field is the number of answers for the first

field, eg:



Yes    10

No        6



To work out the total points for Yes, and No, you'll have to do a JOIN on

your Questions table:



SELECT

    a.Answer,

    SUM(b.Points) AS Score

FROM

    Answers a

INNER JOIN

    Points b

ON

    a.QuestionID = b.QuestionID

WHERE

    a.UserID = 1

GROUP BY

    a.Answer



This again should return two records, with two fields. the first field is

Yes/No, and the second field is the sum of points for all the Yes or No

answers. You might be able to mash the two queries above into 1 query, but I

haven't tested that.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

From: <stephen.eighmey@s...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, February 26, 2002 5:50 PM

Subject: [asp_databases] getting score help please...





: hello,

:

:    i could really use some help with this one...i have a form with 50

: questions. 40 of these questions have checkboxes with either "yes" or "no"

: as the answer. these yes and no answers also have a weighted value, i.e.,

: whether you checked yes or no for, example, question 5, it has a value of

: 2. same thing with any other question but it might have a different value.

: (these values are stored in a seperate table in my database)

:

: what has to happen after this form is submitted is that i need to find out

: how many questions are yes and how many are no. the code i already have

: puts the data correctly into my database. i have to total the point values

: for all the yes answers and total the point values for all the no answers

: then i have to take these figures and divide them to get my score.i need

: to do this from the data that i just put in the database rather than from

: looping through the actual form.

:  can somebody suggest or give an example of a way that i can do this? i'm

: kind of a newbie at this so any code examples would be really great




$subst('Email.Unsub').

ken,



   thank you so much for your help, unfortunately my tables are not set up 

like that. i have 1 table where the yes and no answers are held, as well 

as all of the other answers a user has entered such as their name, 

supervisor, etc.(this is my recordset). the yes and no answers are held in 

34 seperate columns (in this same record) with the headings of Q1 through 

Q34. the values of Q1 through Q34 will be either yes, no, or nothing.



the values for the point values for the same questions are held in a 

different table with 2 columns titled questionID and value. the questionID 

field has Q1 through Q34 listed and the values column has the point values 

for these individual questionID's. my code so far is...



pointValueYes = 0

pointValueNo = 0



set rs = "SELECT * FROM vsatwbt.callCenter WHERE TS_QAID = '"& TS_QAID &"'"

for i = rs("Q1") to rs("Q34")

do while not rs.EOF

rs.moveFirst

if i = "yes" then

myConnCheck = "SELECT * FROM vsatwbt.pointValue WHERE questionID = 'i'"

pointValueYes = pointValueYes + (rs("value"))

else if i = "no" then

pointValueNo = pointValueNo + (rs("value"))

end if

rs.movenext

loop







newTotal = pointValueYes + pointValueNo

varScore = pointValueYes/newTotal



(just above all of this code is my code that (correctly) puts this 

information into the database; i'm leaving the recordset open to perform 

my code that i am showing here)



does it seem like i'm even on the right track to acheive the results i am 

looking for?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: <stephen.eighmey@s...>

Subject: [asp_databases] Re: getting score help please...





:    thank you so much for your help, unfortunately my tables are not set up

: like that. i have 1 table where the yes and no answers are held, as well

: as all of the other answers a user has entered such as their name,

: supervisor, etc.(this is my recordset). the yes and no answers are held in

: 34 seperate columns (in this same record) with the headings of Q1 through

: Q34. the values of Q1 through Q34 will be either yes, no, or nothing.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Personally I think you should reconsider your database design (especially in

light of the queries that you are having to run).



As you can see, you're having to write all sorts of messy (and expensive)

looping code to work out (for example) the number of yes and no answers,

when this could be done for a fraction of the cost by the database engine...



...now you did ask if there was anything wrong with your code. I don't know

if the indenting was lost during delivery, but if it wasn't indented the the

first place I suggest you start. Anyway, there is a fundamental problem with

the code below: The outer loop continues as long as the Recordset is not

.EOF, but everytime you start the loop you move to the first record in the

recordset. Hence you never get to the end of the recordset, hence the loop

never finishes



<%

Do While Not rs.EOF



    rs.MoveFirst

    ...

    rs.MoveNext



Loop

%>





Cheers

Ken