What I need is a query that will show the total Users who have Completed each Topic/Question.
these are my tables:
ansID, questionID, userName, AnsStatus, ans1, ans2, ans3
qID, qNum, q1, q2, q3
topicID, topicTitle, topicType, case_stem, modID, notesToOwner, topicOwner, categories, DueDate, quesID, topicStatus
modID i another table where I grab the module name that is on a form with a dropdown. I don't care about the module name for this solution.
Basically the way this works is that all students are presented with topics and questions. The only time I capture the student username is after they have submitted the topic and answered the questions. I am using Active Directory FBA login. I didn't want to enumerate hundreds of users who log on and and then add them to SQL Table ,so I came up with this system. Not great but it works.
So all students who log onto the application see all topics and questions.
This is what i have that grabs the number of completed answers by user:
SELECT uName,COUNT(anID) As "TotalCount" FROM tbl_answers
Where ansStatus = 'Completed'
GROUP BY uName
then I have this query that when user is selected from the above query I get this:
SELECT topicID, topicTitle, case_stem, topicType, qID, qNum, q1, q2, q3, questionID, anID, uName, an1, an2, an3
FROM topic_Unit, tbl_questions, tbl_answers
WHERE tbl_answers.questionID = tbl_questions.qID
AND tbl_answers.ansStatus = 'Completed'
AND topic_Unit.quID = tbl_questions.qID
AND tbl_answers.uName = @uName
This query also gives me the total topics that a user has remaining:
SELECT topicID, topicTitle, modID, dueDate, moduleName, quID
FROM topic_Unit, tbl_Modules
WHERE tbl_Modules.moduleID = topic_Unit.modID
AND topic_Unit.dueDate >='6/27/2012' AND topic_Unit.dueDate <= '7/24/2012'
and topic_Unit.quID not in (select questionID from tbl_answers where uName= @user)
AND topic_Unit.topicType = 'conf' AND topic_Unit.status = 'Available'
Order By topic_Unit.dueDate Asc
the above works great, but now I need to show
| total users
I want to group by topicTitle (distinct) if possible to get all the topics by name.
Some title will be dupes.
thanks for the help!