Note: 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_answersWhere ansStatus = 'Completed'GROUP BY uName
This query also gives me the total topics that a user has remaining:
SELECT topicID, topicTitle, modID, dueDate, moduleName, quIDFROM topic_Unit, tbl_ModulesWHERE tbl_Modules.moduleID = topic_Unit.modIDAND 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