Pass query without repeat

[ombina]

Hello all,

Thanks for reading this question.

I would like to know how to program this query: Scenario -

SQL Query
.
.
Do While Not rs.eof
.
.
rs.moveNext
loop

Typical, but what if I wanted the results like this -

Product Name
============
Cat Food
Dog Food
etc...

'Next groupings
Cat Food
Brand #1
Brand #2
Brand #3
Total 50

Dog Food
Brand #1
Brand #2
Brand #3
Total 100

So the most important I am looking for is to make it so that it would not repeat Product Name as it loops around.

I appreciate the tips.

Thanks,

Ricky








Thanks,

Ricky Ombina
ombina@yahoo.com

http://www.ombina.com

 

[Tarwn]

Not to difficult:
1) SQL Query
2) Execute into RS
3) Declare a temproray variable to hold catagory
4) Do Until rs.eof
5) if tempCatagory <> rs(&quot;catagory&quot then
a) output the catagory
b) set the tempCat variable = rs(&quot;catagory&quot
6) output the record as usual
7) rs.MoveNext
8) Loop
etc.

Now if you want to total for each catagory have a running total variable that you increment in step 6.5
output this variable in step 5 before substep a as the total for the previous catagory, then output it again after you get out of the loop, so it would look like this:
1) SQL Query
2) Execute into RS
3) Declare a temporary variable to hold catagory (tempCat) and initialize to &quot;&quot;
3.5) declare runningTotal and set to 0
4) Do Until rs.eof
5) if tempCatagory <> rs(&quot;catagory&quot then
a.5) if tempCat <> &quot;&quot; then output a row with total for last catagory
a) output the catagory
b) set the tempCat variable = rs(&quot;catagory&quot
6) output the record as usual
6.5) increment the total from whatever field in the recordset or increment by 1 for a recordcount in this catagory
7) rs.MoveNext
8) Loop
9) if tempCat <> &quot;&quot; then output a row with total for last catagory


Hope that was what you were looking for,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html

 

[ombina]

Here's the code how HTML should display. As you can see the category Title is based on the first character of the list data.

<div align=&quot;center&quot;>
<center>

<table BORDER=&quot;0&quot; CELLPADDING=&quot;5&quot; WIDTH=&quot;600&quot; bordercolorlight=&quot;#FFFFFF&quot; bordercolordark=&quot;#079800&quot; bordercolor=&quot;#FFFFFF&quot;>


<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot;>
<p ALIGN=&quot;CENTER&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>A&nbsp;</font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot;>
<p></p>
</td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#F1FFF0&quot;>
<p ALIGN=&quot;LEFT&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>A15 </font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#EFEFEF&quot;><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;CENTER&quot;>
92936
</font></td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#F1FFF0&quot;>
<p ALIGN=&quot;LEFT&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>A18 </font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#EFEFEF&quot;><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;CENTER&quot;>
24440506
</font></td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#C0C0C0&quot;><b><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;LEFT&quot;>TOTAL</font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#C0C0C0&quot;>&nbsp;</td>
</tr>
<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot;>
<p ALIGN=&quot;CENTER&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>C</font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot;>
<p></p>
</td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#F1FFF0&quot;>
<p ALIGN=&quot;LEFT&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>CAU </font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#EFEFEF&quot;><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;CENTER&quot;>
5807
</font></td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#F1FFF0&quot;>
<p ALIGN=&quot;LEFT&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>CB </font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#EFEFEF&quot;><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;CENTER&quot;>
55046
</font></td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#F1FFF0&quot;>
<p ALIGN=&quot;LEFT&quot;><b><font face=&quot;Arial&quot; size=&quot;1&quot;>CM </font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#EFEFEF&quot;><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;CENTER&quot;>
3261901
</font></td>
</tr>

<tr>
<td WIDTH=&quot;65%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#C0C0C0&quot;><b><font FACE=&quot;Arial&quot; SIZE=&quot;1&quot;>
<p ALIGN=&quot;LEFT&quot;>TOTAL</font></b></td>
<td WIDTH=&quot;20%&quot; HEIGHT=&quot;10&quot; bgcolor=&quot;#C0C0C0&quot;>&nbsp;</td>
</tr>

</table>

</center>
</div> Thanks,

Ricky Ombina
ombina@yahoo.com

http://www.ombina.com