SQL Count and Group By

I am connecting to an MS Access database. I want to count the occurrences of responses, including records with NO response. My SQL statement is counting and providing results. The problem is, it is displaying 2 results for records with NO RESPONSE.

My SQL statement is:

sqlTitle = "select iif(len(LibManTitle)=0 or LibManTitle is null,'No Response',LibManTitle) as LibManTitle_Title, count(*) as LibManTitleCount " & _
      "FROM tblStaff " & _
      "GROUP BY LibManTitle "

      Set objTitle = Server.CreateObject("ADODB.Recordset")
      objTitle.Open sqlTitle, objConn


My display is:

No Response: 8
No Response: 24
Administrative: 18

My code to display results:

while not objTitle.eof
      Response.Write "<ul>"
      Response.Write "<li>" & objTitle("LibManTitle_title") & ": " & objTitle("LibManTitleCount") & "</li>"
      objTitle.movenext
      Response.Write "</ul>"
wend
Malloy1446Asked:
Who is Participating?
 
ianmills2002Connect With a Mentor Commented:
You have to put iif(len(LibManTitle)=0 or LibManTitle is null,'No Response',LibManTitle) in the Group by clause

i.e.

sqlTitle = "select iif(len(LibManTitle)=0 or LibManTitle is null,'No Response',LibManTitle) as LibManTitle_Title, count(*) as LibManTitleCount " & _
      "FROM tblStaff " & _
      "GROUP BY iif(len(LibManTitle)=0 or LibManTitle is null,'No Response',LibManTitle) "
0
 
Malloy1446Author Commented:
THanks for the quick response. TO make the No response appear at the top of the list, I just added a space in front of the ' No Response'.
0
All Courses

From novice to tech pro — start learning today.