Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: Help with Lookup Tables

    Hi all

    I have a web page with a questionaire on it with 5 questions.
    Each question has a choice of 6 answers and each question has the same 6 choices.

    I have a drop down list under each question with the 6 choices on it.
    The drop down lists get their data from a table in SQL Server Database and the results are bound to another table.

    I am now configuring a results page for my manager to look at. The requirements are:

    Each answer has a score depending upon the answer chosen. 0 - 5 for the 6 answer choices.
    I then have a formula to work to which is:
    (Q1Answer + Q2Answer + Q3Answer) /3 * Q4Answer * Q5Answer.

    The result of this formula is then converted to a grade (A - E).

    The problem I have is treating each answer as a number to perform the formula because they are strings. The result is then put in a column called Score.

    I have worked out how to do the grade but can't work out how to do the scoring part.

    I did the grade using a query and a lookup table and tested it by adding a score to the score column manually and it gave the correct grade every time.

    My code is
    Code:
    SELECT 
    tblsdstaffsurvey.Name, tblsdstaffsurvey.Q1response,  
    tblsdstaffsurvey.Q2response,  tblsdstaffsurvey.Q3response, 
    tblsdstaffsurvey.Q4response,  tblsdstaffsurvey.Q5response,  tblsdstaffsurvey.Score, Grades.Grade
    FROM tblsdstaffsurvey
    
    LEFT OUTER JOIN Grades
    ON
    tblsdstaffsurvey.Score BETWEEN Grades.Floor AND Grades.Ceiling
    I now need the column score to be generated from the formula.

    My manager needs to see the answers each person gave, the total score and the grade.

    tblsdstaffsurvey table has an ID column (int), a Name column (varchar(25)), 5
    Qresponse columns (varchar(50)), a Score column (float), a Grade column (char(1)).

    Grades table has an ID column (int), a Floor column (decimal), a ceiling column (decimal), a Grade column (char(1)).

    I hope someone out there can help me as I am pulling my hair out.

    This is my first post on the forum so forgive me if things aren't clear

    Andy

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    first thing that struck me here... Is questions are scored 0-5, and someone gets 0 for either Q4 or Q5, their overall score will be zero for the test....

    Doesn't seem quite fair to me!
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, you do realise that yaur design is really awkward, right? Normalise!!
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this any good?
    Code:
    DECLARE @survey table (
       id   int
     , name varchar(25)
     , q1   varchar(50)
     , q2   varchar(50)
     , q3   varchar(50)
     , q4   varchar(50)
     , q5   varchar(50)
    )
    INSERT INTO @survey (id, name, q1, q2, q3, q4, q5)
          SELECT 1, 'George', '5', '5', '5', '5', '5'
    UNION SELECT 2, 'Poots' , '1', '2', '3', '4', '5'
    UNION SELECT 3, 'Rudy'  , '0', '5', '5', '2', '2'
    UNION SELECT 4, 'Sean'  , '5', '4', '4', '0', '4'
    
    DECLARE @grades table (
       id     int
     , lbound float
     , ubound float
     , grade  char(1)
    )
    INSERT INTO @grades (id, lbound, ubound, grade)
          SELECT 1,   0,  25, 'E'
    UNION SELECT 1,  26,  50, 'D'
    UNION SELECT 1,  51,  75, 'C'
    UNION SELECT 1,  76, 100, 'B'
    UNION SELECT 1, 101, 125, 'A'
    
    
    SELECT survey_results.name
         , survey_results.result
         , grades.grade
    FROM   (
            SELECT survey.name
                 , ((survey.q1 + survey.q2 + survey.q3) / 3.0) * survey.q4 * survey.q5 As [result]
            FROM   (
                    SELECT name
                         , Convert(int, q1) As [q1]
                         , Convert(int, q2) As [q2]
                         , Convert(int, q3) As [q3]
                         , Convert(int, q4) As [q4]
                         , Convert(int, q5) As [q5]
                    FROM   @survey
                   ) As [survey]
           ) As [survey_results]
     INNER
      JOIN @grades As [grades]
        ON survey_results.result BETWEEN grades.lbound AND grades.ubound
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •