Display results of a GROUP BY sort

I am using an MSAccess db. I have a SQL statement selecting the records based on two factors:
tblGeneral.Designation = 'Yes'
tblGeneral.Status = 'Closed'

I then want to display city, state grouped alphabetically by the Complexity [field].

The final report should look like:

Complexity: *
- Cleveland (Brecksville)
- Kerrville
- Menlo Park
- Pittsburgh (Highland Park)

Complexity: 1A
- Birmingham
- Denver
- Nashville
- San Anotonio
- West Roxbury

Complexity: 1C
- Iowa City

Complexity: 2
- Montgomery
- Wichita

Complexity: 3
- Dublin
- Rosebury

Code is generating the following error:
Microsoft JET Database Engine error '80040e21'

You tried to execute a query that does not include the specified expression 'ChartID' as part of an aggregate function.

I am attaching a sample database and the code.
Complexity.txt
complexity.mdb
JLohmanAsked:
Who is Participating?
 
joaoalmeidaCommented:
Try the following


old_complexity =""

Response.Write "<ul>"
while not objRS.eof

      complexity = objRS("Complexity")
      if  old_complexity <> complexity  then  
            if old_complexity <> "" then
                Response.Write "</ul>"
            end if
            Response.Write "<li>" & objRS("Complexity")   & "</li>"
            Response.Write "<ul>"
        end if

       Response.Write "<li>" & objRS("City") & "</li>"

             
        old_complexity = complexity  

         objRS.MoveNext        
wend
Response.Write "</ul>"
0
 
awking00Commented:
Complexity.txt is showing up as empty.
0
 
JLohmanAuthor Commented:
I'll attach again.
Complexity.txt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
alorentzCommented:
Run the query in Access first, then use in code.  You have to group by all the field, unless they are aggregated in some fashion.

 sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.Complexity, " & _
       "tblGeneral.Status, tblGeneral.Designation " & _
      "FROM tblGeneral " & _
      "WHERE tblGeneral.Designation = 'Yes' AND tblGeneral.Status = 'Closed' " & _
      "GROUP BY complexity, chartid, city, state, status, designation "
0
 
JLohmanAuthor Commented:
The complete list of cities is listed but they are not grouped by the Completxity field. Following is my display code:

      while not objRS.eof
          Response.Write "<ul>"
          Response.Write "<li>" & objRS("Complexity")
                
         
                  while not objRS.eof
                        Response.Write "<ul>"
                      Response.Write "<li>" & objRS("City") & "</li>"
                  objRS.MoveNext
                  Response.Write "</ul>"
                  Wend
         
         
          objRS.MoveNext
          Response.Write "</ul>"
      wend
0
 
alorentzCommented:
So you got the query to run with my changes, correct?

The grouping you mention will be in display coding, as well as ordering by complexity.  You need to store the complexity in a variable in the loop, an check that variable in each loop against the current complexity value, to see if it is different.  If it is different, then regroup (close and open new HTML grouping).
0
 
alorentzCommented:
For example: (this is concept only)
   
   starter = 0
   opener = 0
   while not objRS.eof
    if starter = 0 then
        old_complexity = objRS("Complexity")
        starter = 1
    end if
     complexity = objRS("Complexity")
      if  old_complexity <> complexity  then  
             if opener = 1 then
                      response.write "</li></ul>"  'close last UL from complexity
             end if
             
             old_complexity = complexity  
             'start a new group for complexity
             
          Response.Write "<ul>"
          Response.Write "<li>" & objRS("Complexity")
          opener=1 'set so that closing html will be included in next loops
      end if

               
         
                  while not objRS.eof
                        Response.Write "<ul>"
                      Response.Write "<li>" & objRS("City") & "</li>"
                  objRS.MoveNext
                  Response.Write "</ul>"
                  Wend
         
         
          objRS.MoveNext
          Response.Write "</ul>"
      wend

Haven't tested ....
0
 
JLohmanAuthor Commented:
Yes, the query does run. My original code to display results starts with the correct indentation, but does not display any of the Complexity group divisions. THe cities are all listed as one group. Your code and mine display basically the same list.
0
 
joaoalmeidaCommented:
There is no purpose in using group by without using aggregation functions.

Try the following SQL.

 sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, tblGeneral.Complexity, " & _
       "tblGeneral.Status, tblGeneral.Designation " & _
      "FROM tblGeneral " & _
      "WHERE tblGeneral.Designation = 'Yes' AND tblGeneral.Status = 'Closed' " & _
      "ORDER BY complexity, state, city, designation "


and use the code that alorentz gave
0
 
JLohmanAuthor Commented:
Joaoalmeia: the ORDER BY clause creates the following error:

     Microsoft JET Database Engine error '80040e14'
     Syntax error in ORDER BY clause.

Actually the code recommended by alorentz is close, the problems:
- it is displaying the complexity for each city. (I want the list to generate one complexity and the associated cities listed)
- it skips the first complexity

Here is the display code I have now:

starter = 0
opener = 0

while not objRS.eof
      if starter = 0 then
            old_complexity = objRS("Complexity")
        starter = 1
    end if

      complexity = objRS("Complexity")
      if  old_complexity <> complexity  then  
            if opener = 1 then
            Response.Write "<ul>"
            Response.Write "<li>" & objRS("Complexity")
            opener=1
            'set so that closing html will be included in next loops
                  Response.Write "<ul>"
                  Response.Write "<li>" & objRS("City") & "</li>"
              Response.Write "</ul>"
              Response.Write "</li>"
            'close last UL from complexity
        end if
             
        old_complexity = complexity  
        'start a new group for complexity
             
            Response.Write "<ul>"
            Response.Write "<li>" & objRS("Complexity")
            opener=1
            'set so that closing html will be included in next loops
                  Response.Write "<ul>"
                  Response.Write "<li>" & objRS("City") & "</li>"
              Response.Write "</ul>"
              Response.Write "</li>"
end if

               
      objRS.MoveNext
            Response.Write "</ul>"
wend
0
 
JLohmanAuthor Commented:
Thank you. I will be able to use this solution in other reports.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.