Solved

SQL Count and Group By

Posted on 2013-06-17
2
643 Views
Last Modified: 2013-06-17
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
0
Comment
Question by:Malloy1446
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 500 total points
ID: 39255090
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
 

Author Closing Comment

by:Malloy1446
ID: 39255096
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question