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
the above works great, but now I need to show topicTitle: | total users | Status
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!
Microsoft DevelopmentMicrosoft SQL Server 2008
Last Comment
harfang
8/22/2022 - Mon
ValentinoV
Would it be possible to post a small script that creates those tables and adds some sample data to them? Data types and field content are really important for this type of question...
great! They both worked. Seems the results are correct. The second query I get an error with the "First" function. I replaced it with MAX or MIN and it worked!
thanks for your help!!
harfang
I'm glad it worked, success with your project! — (°v°)