Calculating survey results... & data model

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating survey results... & data model

Author

Topic

jenese
Starting Member

6 Posts

Posted - 2002-09-24 : 12:11:08

Hi,
I'm looking for tips, links & suggestions. Would appreciate any info.

I'm developing an online survey using ASP and a SQL database. I don't have any problems connecting to the database or writing code... but I've never done a survey before.

My first question is how to calculate the survey results?

My second question ... how best to organize the tables?

I'm assuming that how I organize the tables will make a difference in how hard/easy it is to calculate the results.... is this true?

(My first goal is just to be able to collect the results and then calulate the percentages of answers. Eventaully, it'd be good to have the survey be created by an admin inputing the survey and it's questions, but that can wait)

This is the structure I have so far:

T_surveys
id, survey_name, start_date, end_date, status, number_of_questions

T_questions
id, survey_id, question_text, display_order

T_question_options
id, survey_id, question_id, option_type, option_text, display_order

T_responses
id, survey_id, question_id, member_id, response, comment, date_entered

this set up would mean that a checkbox type question could have "1,3,6,7" in the response field, which could make this way of calculating the results harder:

select _something_
from T_responses
where survey_id = @survey_id and question_id = @question_id

...out of time for now! will come back to this.
Thanks.

jenese
Starting Member

6 Posts

Posted - 2002-09-24 : 17:11:49

Ok, the three calculations needed, in rough form:

1) Get __ total survey responses
select count(*) as total_responses
from T_responses
where survey_id = @survey_id and question_id = @question_id

2) Get __ number of responders who selected ___ option
select count(*) as response_A
from T_responses
where survey_id = @survey_id and question_id = @question_id
and T_responses.response = @option_value (found in T_question_options.option_value, maybe it'd be easier to loop though available options for each question...)

3) Get __ percentage of responders who selected ___ option
percent_response_A = response_A / total_responses

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources