?
Solved

Display results of a GROUP BY sort

Posted on 2012-08-14
11
Medium Priority
?
676 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:JLohman
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 38293238
Complexity.txt is showing up as empty.
0
 

Author Comment

by:JLohman
ID: 38293421
I'll attach again.
Complexity.txt
0
 
LVL 31

Expert Comment

by:alorentz
ID: 38293989
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JLohman
ID: 38294115
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
 
LVL 31

Expert Comment

by:alorentz
ID: 38294126
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
 
LVL 31

Expert Comment

by:alorentz
ID: 38294156
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
 

Author Comment

by:JLohman
ID: 38294183
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
 
LVL 9

Expert Comment

by:joaoalmeida
ID: 38294614
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
 

Author Comment

by:JLohman
ID: 38294644
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
 
LVL 9

Accepted Solution

by:
joaoalmeida earned 2000 total points
ID: 38294679
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
 

Author Closing Comment

by:JLohman
ID: 38294719
Thank you. I will be able to use this solution in other reports.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

840 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