SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot egiblock's Avatar
    Join Date
    Aug 2001
    Location
    Mentor, Ohio
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    group records. isn't happening

    my statement:
    SELECT Country_Name, State_Name, Chapter_Name FROM qry_chapters GROUP BY Country_Name, State_Name, Chapter_Name

    output:

    Country 1
    State 1
    Record 1


    i want the output to be:

    Coutry 1
    State 1
    Record 1
    Record 2
    State 2
    Record 1
    Record 2
    Country 2
    etc.etc..



    i can't get the grouping to work at all. can someone help me out here, or point me in a direction to get this thing to work?


    thanks

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    from the look of things, appears your code is correct to me.

    But try SELECT Country_Name as country1, State_Name as state1, Chapter_Name as record1 FROM qry_chapters GROUP BY Country_Name, State_Name, Chapter_Name

    hope this helps

  3. #3
    SitePoint Zealot egiblock's Avatar
    Join Date
    Aug 2001
    Location
    Mentor, Ohio
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here's the demo and code:

    http://www.webalterations.com/vctest/vcChapters.asp

    I need it to group by "United States" (Country_Name), and then by State_Name in the output


    Code:
    <%
    Dim getChapters
    Dim getChapters_numRows
    Set getChapters = Server.CreateObject("ADODB.Recordset")
    getChapters.ActiveConnection = MM_vctest_STRING
    getChapters.Source = "SELECT Country_Name, State_Name, Chapter_Name FROM qry_chapters GROUP BY Country_Name, State_Name, Chapter_Name"
    getChapters.CursorType = 0
    getChapters.CursorLocation = 2
    getChapters.LockType = 1
    getChapters.Open()
    getChapters_numRows = 0
    %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style7 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: x-small; }
    .style12 {font-family: Verdana, Arial, Helvetica, sans-serif; font-weight: bold; font-size: x-small; }
    .style13 {font-size: x-small}
    -->
    </style>
    </head>
    <body>
    <p>VC Chapter Listing</p>
    <table border="0" cellspacing="0" cellpadding="0">
    <%
    Do while NOT getChapters.EOF
    %>
     
    <tr>
    	 <td><span class="style12"><%=(getChapters.Fields.Item("Country_Name").Value)%></span></td>
    	 <td><span class="style13"></span></td>
    	 <td><span class="style13"></span></td>
    </tr>
    <tr>
    	 <td><span class="style13"></span></td>
    	 <td><span class="style12"><%=(getChapters.Fields.Item("State_Name").Value)%></span></td>
    	 <td><span class="style13"></span></td>
    </tr>
    <tr>
    	 <td><span class="style13"></span></td>
    	 <td><span class="style13"></span></td>
    	 <td><span class="style7"><a href="vcMembers.asp?ID="><%=(getChapters.Fields.Item("Chapter_Name").Value)%></a></span></td>
    </tr>
    <%
    getChapters.MoveNext
    Loop
    %>

  4. #4
    SitePoint Addict sinapra's Avatar
    Join Date
    Sep 2004
    Location
    secunderabad
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the output is the result of the loop you have put. Though the query is alright since you want to group country, state and chapter, and say you have two chapters in a country so the country and state would repeat in the resultset. Instead, you can categorize the main groups into another loop and show the result for each category in a seperate loop, you need to use two queries, i.e.; you can select country once and then the related chapters.
    i hope my description helps .
    Regards

    Its Great to work and Be Merry!!!!!!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •