I have a table that holds answers and another table that holds student responses. The answer table can have multiple 'M' answers for an item(question). I'm trying to figure out how many 'M' responses each student got for a particular question.
<!--- This will pull all answers for this test --->
<cfquery name="getAnswers" datasource="#request.datasource#">
SELECT itemID, answer, answerID
FROM answers a
INNER JOIN itemtoItemset i on i.item = a.itemID
WHERE i.itemsetID = 60
AND a.answerID ='M'
<!--- This will pull all students who took this test --->
<cfquery name="getStudentResponse" datasource="#request.datasource#">
WHERE itemsetID = 60
ORDER BY studentID
Can someone tell me the best way to get a count for each individual student? Initially I had written a query that had the count function, but with the multiple 'M' responses for an individual ItemID(question) it was throwing my counts off.
Any help would be appreciated