> I have a table with columns named q1,q2,q3, through q19. Each > question can have a value of 1, 2, or 3. How can I generate a > query/cfcode that will tell me how many people answered 1, 2, and 3 > for question 1 through question 19 without having a ton of queries? > I know it needs to loop somehow, but it just hasn't worked out > correctly so far.
Another approach you might consider would be to normalize your data so instead of having 19 columns representing answers to specific questions for each respondant, you would have a table with up to 19 rows for each respondant, each with a question ID (1-19) and the answer (1-3) such as: userID, qID, qAnswer For example, assume the following data in such a table: userID qID qAnswer 1 1 1 1 2 2 1 3 3 1 4 3 2 1 3 2 2 3 3 1 1 3 2 1 Than a query like: select qID, qAnswer, count(userID) as QACnt from myTable group by qID, qAnswer would result in: qID qAnswer QACnt 1 1 2 1 3 1 2 2 2 2 3 1 3 3 1 4 3 1 This would show you how may (QACnt) had answered each question with which response (Question 1 had 2 responses for answer 1 and 1 response for answer 3). Regards, Stephen ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
