Nested loop/if...then table display problem

[Krickles]

I am returning a recordset with the following query:

SELECT sdl.supplier_division_id,
       s.supplier_id,
       s.supplier_name,
       sdl.status_id,
       sl.status_desc
FROM   supplier AS s
INNER JOIN supplier_division_lu AS sdl ON sdl.supplier_id = s.supplier_id
INNER JOIN division_lu AS d ON d.division_id = sdl.division_id
INNER JOIN status_lu AS sl ON sl.status_id = sdl.status_id
ORDER BY s.supplier_name

The query returns the correct information. However, I am having a difficult time displaying the information. Currently, I am using the following ASP/HTML:

Set rsGetList = objRec
rsGetList.MoveFirst

...some html code with <table> and <th> 

Do While NOT rsGetList.EOF
Response.Write (&quot;<TD>&quot;)
Response.Write rsGetList(&quot;supplier_name&quot;)
     IF varSupplier_ID = rsGetList(&quot;supplier_id&quot;) THEN
          Response.Write (&quot;<TD>&quot;)
          Response.Write (&quot;<INPUT type = checkvox id=checkbox1 name=&quot;)
          Response.Write (&quot;'&quot; & rsGetList(&quot;division_name&quot;) & &quot;'&quot;)
          Response.Write (rsGetList(&quot;status_desc&quot;))
          Response.Write (&quot;>&quot;)
          Response.Write rsGetList(&quot;division_name&quot;)
          Response.Write (&quot;<BR>&quot;)
          Response.Write (&quot;</TD>&quot;)
     END IF
Response.Write (&quot;<TD>&quot;)

...html buttons

Response.Write (&quot;</TR&quot;>)
rsGetList.MoveNext
Loop

The display is a table diplays the following:

SUPPLIER A | DIVISION 1 | BUTTONS
-------------------------------------------
SUPPLIER A | DIVISION 2 | BUTTONS
-------------------------------------------
SUPPLIER A | DIVISION 3 | BUTTONS
-------------------------------------------
SUPPLIER B | DIVISION 1 | BUTTONS
-------------------------------------------
SUPPLIER B | DIVISION 2 | BUTTONS

I would like the table to display as follows:


| DIVISION 1 |
SUPPLIER A | DIVISION 2 | BUTTONS
| DIVISION 3 |
-------------------------------------------
SUPPLIER B | DIVISION 1 | BUTTONS
| DIVISION 2 |

Please advise. Kind regards, Krickles

 

[lebisol]

as always basic design is easier when seen by itself.Make a clean simple new page and make a table ...then arrange thing the way u like it....the look at the code...then use ASP to generate the same (much like u had witht the response.write's)
key word is "colspan" for your problem
here is the table:
------------------------------------snip--------
<table width=&quot;332&quot; height=&quot;81&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; id=&quot;TEST_TABLE&quot;>
<tr>
<td colspan=&quot;3&quot; align=&quot;center&quot;>HELLO</td>
</tr>
<tr>
<td width=&quot;127&quot; align=&quot;center&quot;>Center</td>
<td width=&quot;127&quot; align=&quot;center&quot;>Center</td>
<td width=&quot;127&quot; align=&quot;center&quot;>Center</td>
</tr>
<tr>
<td colspan=&quot;3&quot; align=&quot;center&quot;>HELLO</td>
</tr>
</table>
-------------------snap_---------------------

now grabb the tags like u have been &quot;<td>&quot; and BR and all
All the best!

 

[Genimuse]

Because it's going to be more of a hassle to calculate the correct rowspan in advance, it would be easier if you'd be satisfied with:
[tt]
SUPPLIER A | DIVISION 1 | BUTTONS
| DIVISION 2 |
| DIVISION 3 |
-------------------------------------------
SUPPLIER B | DIVISION 1 | BUTTONS
| DIVISION 2 |
[/tt]
Note that &quot;SUPPLIER A&quot; and &quot;BUTTONS&quot; have moved up a line.

If that's satisfactory then you need merely create a variable, let's call it CurrentSupplier, and another called ButtonsNeeded.

Then your basic logic is (pseudocode-ish):

CurrentSupplier = ""
ButtonsNeeded = False

Start Loop
    If rs(&quot;supplier_name&quot;) <> CurrentSupplier Then
        Write the supplier_name
        CurrentSupplier = rs(&quot;supplier_name&quot;)
        ButtonsNeeded = True
    Else
        Write &amp;nbsp;
    End If
    Write Division info
    If ButtonsNeeded Then
        Write buttons
    Else
        Write &amp;nbsp;
    End If
Loop

If the Supplier and the Buttons need to be centered in a rowspan then instead you'll need to hold off on writing the Supplier and the Buttons, build a string that contains the division cells only along with a count of the rows. Then you'd write out the Supplier with the correct rowspan, then your concatenated Division string, then your Buttons with the correct rowspan.

Make sense?

 

[Genimuse]

Oops, forgot the very important:
[tt]
ButtonsNeeded = False
[/tt]
that should appear right below the line where you Write buttons.

 

[Krickles]

Lebisol - thanks for the post, but I separated the html from asp and the table worked fine. When I insert the rowspans (not colspan) the table stretches to the right since the supplier_name is repeated for each division.

Genimuse - thanks, too for the post. I tried your code and had some success, but still not the end result I expected. The buttons did become dynamic, but the overall display did not change.

Some clarification...

I want to group by supplier_name and show all related division_names, then buttons. So, the display will be:

Row 1: <TH>text</TH>
Row 2: Supplier A | group of division_name checkboxes | group of buttons
Row 2: Supplier B | group of division_name checkboxes | group of buttons

The entire group of divisions should display for each supplier, but need to be specific to each supplier. The check box next to each division allows users to know if the particular supplier is on/off for the division in question.

Hope this makes sense. Please let me know if I need to try and better clarify.

Kind regards, Krickles

 

[Krickles]

Oops

The row example should be as follows:

Row 1: <TH>text</TH>
Row 2: Supplier A | group of division_name checkboxes | group of buttons
Row 3: Supplier B | group of division_name checkboxes | group of buttons

-Krickles

 

[Genimuse]

It's the same idea as the second concept I posted but simpler and with a backward look. Here's the code/pseudocode:

Dim CurrentSupplier, DivisionList

'Set the current supplier to the first one in the list
CurrentSupplier = rs("supplier_name")

Start Loop
    If CurrentSupplier <> rs(&quot;supplier_name&quot;) Then
        Write CurrentSupplier
        Write DivisionList
        Write Buttons
        CurrentSupplier = rs(&quot;supplier_name&quot;)
        DivisionList = rs(&quot;division_name&quot;)
    Else
        DivisionList = DivisionList & checkbox & rs(&quot;division_name&quot;)
    End If
End Loop

'Write out the last one
Write CurrentSupplier
Write DivisionList
Write Buttons

If your supplier list was A A B C C then the first time through A = A so it builds the first part of the DivisionList. Next time A = A so it adds more to the DivisionList. Then the next time A <> B so it print out supplier A, its divisions, and the buttons (opening and closing the <TR> all in this write). It sets CurrentSupplier to B and the new DivisionList to the first division under B. The next time through B <> C so it prints out all of the B stuff and sets CurrentSupplier to C and the new DivisionList to the first division in C. The last time through the loop C = C so it adds onto DivisionList. The loop is complete but the very last supplier hasn't been written yet (C), so we write out the last one.