Ok, I have one last query to get right in my eval app. Here's my schema: http://wtomlinson.com/evalschema.gif
Jim has been kind enough to help work out my queries. Now, I just need to rollup my rankings questions and I'm done (mostly). The data looks like this: tblquestions: Rank these tools in order of learning curve tblanswersets CF ..NET PHP tblanswers 1 2 3 Answer get inserted into an output table, tblevalanswerresults, where they can be counted. Here's what I need in plain english: Show me a list of questions. With each question, show me a list of the answers according to what they were ranked, listed highes ranked to lowest. Here's a query I'm workin with, the data isn't right tho. It just counts up the answers for each answerset. <cfquery name="getRankedQuestionReport" datasource="#SESSION.DSN#"> SELECT Q.questionID, Q.questionsetID, Q.question, Q.questioncatid, QAX.questionid, QAX.answersetid, ASets.answersetid, ASets.answersettypeid, ASets.answersetname, AST.answersettypename, ASAX.answersetid, ASAX.answerid, A.answerID, A.answername, A.answervalue, (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND e1.questionid = Q.questionID) AS totalforthisquestion, (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = Q.questionID) AS totalforinstructor, ((SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND e1.questionid = Q.questionID)/ (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = Q.questionID)*100) AS answerpercentage FROM tblquestions Q INNER JOIN tblquestionsanswers_x QAX ON Q.questionID = QAX.questionID INNER JOIN tblanswersets ASets ON QAX.answersetid = ASets.answersetid INNER JOIN tblanswersettypes AST ON AST.answersettypeid = ASets.answersettypeid INNER JOIN tblanswersetsanswers_x ASAX ON QAX.answersetid = ASAX.answersetid INNER JOIN tblanswers A ON ASAX.answerid = A.answerid WHERE Q.questioncatid = 3 /* Additional Q = 3*/ AND ASets.answersettypeid = 3 /* Ranking answers only = 3*/ </cfquery> Thanks much, Will ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275469 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
