Will, Which DB are you using and if it's not difficult can you also post db scripts to create tables and populate some sample data.?
Thanks Qasim On 3/20/07, Will Tomlinson <[EMAIL PROTECTED]> wrote: > > Ok, I'm really close on this. I've been workin on it for hours, changing > queries, not gettin the results I need. > > I filled out one survey of 3 questions, each question has 5 possible > answers. > > Here's the query: > > SELECT > Q.questionID, > Q.questionsetID, > Q.question, > Q.answersetid, > ARS.answersetid, > ARS.answersetname, > A.answerID, > A.answersetID, > A.answername, > A.answersortnum, > A.answervalue, > EAR.evalID, > EAR.answerID, > E.evalID, > E.InstructorID, > COUNT(EAR.answerID) AS numAnswers > FROM tblquestions Q > INNER JOIN tblanswersets ARS USING (answerSetID) > INNER JOIN tblanswers A USING (answerSetID) > LEFT JOIN tblevalanswerresults EAR USING (questionID) > INNER JOIN tblEvaluations E > ON E.evalID = EAR.evalID > WHERE E.InstructorID = '#SESSION.CollegeID#' > GROUP BY Q.questionID, A.answerID > > HEre's the results, with my <cfoutput grouping> > > http://wtomlinson.com/evalQRYs/qryData2.gif > > It should return 0 for 4 answers out of 5, since I can only answer one of > five possible answers. But it's counting 1 for each answer - not right. I > think I can tell what's happening, but don't know how to fix it. It's like > it's grouping off the tblAnswers table, but should be calculating rows from > the tblEvalAnswerResults table. > > And here's my DB schema if it helps: > > http://wtomlinson.com/evalDBSchema.jpg > > Thanks!! > > Will > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273231 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
