Group By Display

[dcwave]

I am not sure how to explain this.
I would like my web page to display some info like this:

Person One Name
Item 1
Item 2
Item 3

Person Two Name
Item 1
Item 2
Item 3


I have a query that pulls all of the data I need from two tables. I have tried using Do While loops in diferent places to get the data.

I get this in one place:

Person One Name
Item 1
Item 2 Item3

And this in another:

Person One Name
Item 1
Person Two Name
Item 1
Person One Name
Item 2

I am not sure what I need to do in order to get the info to display correctly. Maybe a different query, maybe a table structure.

You folks have been great at answering my other questions... thanks alot!

<%
	strMonth = Request.QueryString("MonthID")
	strSQL = "SELECT rsm.FName, rsm.LName, Month([DateSold]) AS MonthSold, sales.CustName, sales.SysConfig, sales.HostVend, sales.Type, sales.Amount, sales.Margin, rsm.ID FROM sales INNER JOIN rsm ON sales.RSMID = rsm.ID WHERE Month([DateSold])=" & strMonth
	set rs = con.execute(strSQL)
	If Not rs.BOF Then
	Do While Not rs.EOF 
%>
<table width="70%" border="0" cellspacing="0" cellpadding="0">
  
<tr>
  <td><%=rs("FName")%></td>
</tr>

<tr>
  <td><%=rs("CustName")%></td>
</tr>

</table>
<%		
		rs.MoveNext 
		Loop
		End If
%>

 

[Tarwn]

Wit the second set of example data you posted you could easily set up a structure in your While loop to output this data.

Basically you first want to ORDER BY the person's name (or whichever field you meant to group the output by). Then when you are looping through your recordset you just keep track of the last value you processed for the grouped by field. If the value changes then starta new row/whatever and print the name/primary grouped value, otherwise just print out the secondary values, like:

Dim last_name
Do Until myRS.EOF
   'if the name has changed
   If myRS("name") <> last_name Then
      Response.Write "<tr><td>" & myRS("name") & "</td>"
      last_name = myRS("name")
   Else 'same person as last record
      Response.Write "<tr><td></td>"
   End If

   'output the secondary values on every loop
   Response.Write "<td>" & myRS("ItemName") & "</td>"

   'end this row
   Response.Write "</tr>"
   myRS.MoveNext
Loop

Thats the basic concept, you could use this methodology for creating all kinds of grouped formats. Hope it helps,

-T

 

[dcwave]

So if I understand, I want to use a GROUP BY in my query string, then use the above code to loop based on a match, if no match go to next name.

 

[Tarwn]

No, not quite. You always want to output all of the records. The idea behind the baove code is that you want to loop through each persons section of the recordset, only outputting a new name when it is differant then the one from the previous record. The GROUP BY in your SQL clause has nothing to do with it, but the ORDER BY is necessary.

basically if you had a record set that looked like:
Bob red
Bob blue
Bob green
Jane white
Jane yellow
Hans brown
Hans violet

On the first loop through your recordset it would output the name because the empty string would be <> Bob so your output would look like:
<tr><td>Bob</td>

Then it would output the item value:
<tr><td>Bob</td><td>red</td></tr>

On the next loop the last_name string would match with "Bob" so it would only display an empty column:
<tr><td>Bob</td><td>red</td></tr>
<tr><td></td><td>blue</td></tr>

Same for the next one:
<tr><td>Bob</td><td>red</td></tr>
<tr><td></td><td>blue</td></tr>
<tr><td></td><td>green</td></tr>

On the next loop we would check the last_name and "Bob" <> "Jane" so it would output this new name and update the last_name variable (and then it would output the item value):
<tr><td>Bob</td><td>red</td></tr>
<tr><td></td><td>blue</td></tr>
<tr><td></td><td>green</td></tr>
<tr><td>Jane</td><td>white</td></tr>


So the idea is that if your ORDER BY the field you want to group your display with, you can then only output that field's value when it changes, creating a pretty little grouped display. On every loop you would always output the secondary value(s), but only output the primary value when it had changed from the previous stored one.

-T

 

[dcwave]

OKay.. that make sense. Thanks much I will give a try and see how it goes.

I know I will have I other question in a day or so for the hardest part - Running Sum based on Year, Display by Month and Rep.
I have it working in access now - its just making sure that it works in ASP..

Thanks much!