Display DB Records Differently

[rn5a]

A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The
Teacher table has 2 columns - TeacherID & TeacherName (TeacherID being
the primary key). The Class table too has 2 columns - ClassID &
ClassName (ClassID being the primary key)

The TeacherClass table has 3 columns - TCID (AutoNumber), TeacherID &
ClassID. One teacher can teach multiple classes & one class can be
taught by multiple teachers. The TeacherClass table basically stores
this info. If TeacherID=1 teaches 3 classes, then the TeacherClass DB
table would look like this (1st column shown below is the TeacherID &
the 2nd column is the ClassID column):

1 3
1 7
1 9

& not as

1 3, 7, 9

Assume that TeacherID=1, whose name is, say, Peter teaches 3 classes
whose ClassIDs are, say, Class-3, Class-7 & Class-9. I have already
framed the following SQL query that will retrieve these records by
JOINing the 3 tables together:

SELECT T.TeacherName,C.ClassName,T.TeacherID,C.ClassID FROM
(TeacherClass AS TC INNER JOIN Teachers AS T ON
T.TeacherID=TC.TeacherID) INNER JOIN Classes AS C ON
C.ClassID=TC.ClassID ORDER BY T.TeacherName

but the problem I am facing is in displaying the records in a HTML
table. I don't want the records to be displayed in the traditional way
whose source code would look something like this:

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>

<tr>
<td>Peter</td>
<td>Class-3</td>
</tr>

<tr>
<td>Peter</td>
<td>Class-7</td>
</tr>

<tr>
<td>Peter</td>
<td>Class-9</td>
</tr>
</table>

This ASP code would produce the above source code of the HTML table
with the records:

<%
'Connection objConn

'here comes the above SQL query
strSQL="SELECT........"

'Recordset objRS
objRS.Open strSQL,objConn
%>

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>
<%
Do Until(objRS.EOF)
%>
<tr>
<td><%= objRS("TeacherName") %></td>
<td><%= objRS("ClassName") %></td>
</tr>
<%
objRS.MoveNext
Loop
%>

But I want the source code of the HTML table to look something like
this:

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>

<tr>
<td valign=middle>Peter</td>
<td>
<ul>
<li>Class-3</li>
<li>Class-7</li>
<li>Class-9</li>
</ul>
</td>
</tr>
<table>

In other words, the 1st cell in the 1st row in the HTML table should
display Peter only once while the corresponding 2nd cell in the 1st
row should display the 3 classes - Class-3, Class-7 & Class-9 as
bulleted lists.

How do I do this?

One way of doing this is to create 2 SQL queries - the 1st one will
retrieve DISTINCT TeacherName from the Teacher table & the 2nd SQL
query will be the one shown above, then create 1 child HTML table
within each of the 2 cells of the parent HTML table & finally display
the DISTINCT TeacherName in the 1st child HTML table (which is within
the 1st cell of the parent HTML table) & then display the
corresponding classes in the 2nd child HTML table (which is within the
2nd cell of the parent HTML table)? Would this be the correct
approach?

Thanks

 

[Adrienne Boswell]

Gazing into my crystal ball I observed (e-mail address removed) writing in

A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The
Teacher table has 2 columns - TeacherID & TeacherName (TeacherID being
the primary key). The Class table too has 2 columns - ClassID &
ClassName (ClassID being the primary key)

The TeacherClass table has 3 columns - TCID (AutoNumber), TeacherID &
ClassID. One teacher can teach multiple classes & one class can be
taught by multiple teachers. The TeacherClass table basically stores
this info. If TeacherID=1 teaches 3 classes, then the TeacherClass DB
table would look like this (1st column shown below is the TeacherID &
the 2nd column is the ClassID column):

1 3
1 7
1 9

& not as

1 3, 7, 9

Assume that TeacherID=1, whose name is, say, Peter teaches 3 classes
whose ClassIDs are, say, Class-3, Class-7 & Class-9. I have already
framed the following SQL query that will retrieve these records by
JOINing the 3 tables together:

SELECT T.TeacherName,C.ClassName,T.TeacherID,C.ClassID FROM
(TeacherClass AS TC INNER JOIN Teachers AS T ON
T.TeacherID=TC.TeacherID) INNER JOIN Classes AS C ON
C.ClassID=TC.ClassID ORDER BY T.TeacherName

but the problem I am facing is in displaying the records in a HTML
table. I don't want the records to be displayed in the traditional way
whose source code would look something like this:

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>

<tr>
<td>Peter</td>
<td>Class-3</td>
</tr>

<tr>
<td>Peter</td>
<td>Class-7</td>
</tr>

<tr>
<td>Peter</td>
<td>Class-9</td>
</tr>
</table>

This ASP code would produce the above source code of the HTML table
with the records:

<%
'Connection objConn

'here comes the above SQL query
strSQL="SELECT........"

'Recordset objRS
objRS.Open strSQL,objConn
%>

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>
<%
Do Until(objRS.EOF)
%>
<tr>
<td><%= objRS("TeacherName") %></td>
<td><%= objRS("ClassName") %></td>
</tr>
<%
objRS.MoveNext
Loop
%>

But I want the source code of the HTML table to look something like
this:

<table border=2>
<tr>
<th>TEACHER NAME</th>
<th>CLASS NAME</th>
</tr>

<tr>
<td valign=middle>Peter</td>
<td>
<ul>
<li>Class-3</li>
<li>Class-7</li>
<li>Class-9</li>
</ul>
</td>
</tr>
<table>

In other words, the 1st cell in the 1st row in the HTML table should
display Peter only once while the corresponding 2nd cell in the 1st
row should display the 3 classes - Class-3, Class-7 & Class-9 as
bulleted lists.

How do I do this?

One way of doing this is to create 2 SQL queries - the 1st one will
retrieve DISTINCT TeacherName from the Teacher table & the 2nd SQL
query will be the one shown above, then create 1 child HTML table
within each of the 2 cells of the parent HTML table & finally display
the DISTINCT TeacherName in the 1st child HTML table (which is within
the 1st cell of the parent HTML table) & then display the
corresponding classes in the 2nd child HTML table (which is within the
2nd cell of the parent HTML table)? Would this be the correct
approach?

Thanks

You can test to see if the next record is the same value, and if it is
not, act accordingly. The problem you might have is getting the markup
correctly generated.

 

[rn5a]

Gazing into my crystal ball I observed (e-mail address removed) writing in
























You can test to see if the next record is the same value, and if it is
not, act accordingly. The problem you might have is getting the markup
correctly generated.

--
Adrienne Boswell at Home
Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
Please respond to the group so others can share- Hide quoted text -

- Show quoted text -

Thanks, Adrienne, for your suggestion but how do I do the test? I mean
to say that objRS("TeacherName") will give me the
TeacherName...OK...fine... but against whom or against what should the
comparison be done to find whether the next TeacherName is the same as
the previous TeacherName or not? Can you please show me a small
example?

Thanks once again,

Regards,

RON

 

[Adrienne Boswell]

Gazing into my crystal ball I observed (e-mail address removed) writing in

Thanks, Adrienne, for your suggestion but how do I do the test? I mean
to say that objRS("TeacherName") will give me the
TeacherName...OK...fine... but against whom or against what should the
comparison be done to find whether the next TeacherName is the same as
the previous TeacherName or not? Can you please show me a small
example?

Thanks once again,

Quick and dirty - adapt as needed:
testarr = array("dog", "cat", "cat", "bird", "horse")

for i = 0 to ubound(testarr)
'response.write "<br />testthingy: " & tempthingy
if trim(tempthingy) = trim(testarr(i)) then
else
response.write "<br />" & i & ": " & testarr(i)
end if
tempthingy = testarr(i)
next
tempthingy = ""

=======================
Results:
0: dog
1: cat
3: bird
4: horse

 

[rn5a]

Gazing into my crystal ball I observed (e-mail address removed) writing in







Quick and dirty - adapt as needed:
testarr = array("dog", "cat", "cat", "bird", "horse")

for i = 0 to ubound(testarr)
'response.write "<br />testthingy: " & tempthingy
if trim(tempthingy) = trim(testarr(i)) then
else
response.write "<br />" & i & ": " & testarr(i)
end if
tempthingy = testarr(i)
next
tempthingy = ""

=======================
Results:
0: dog
1: cat
3: bird
4: horse

--
Adrienne Boswell at Home
Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
Please respond to the group so others can share- Hide quoted text -

- Show quoted text -

Thanks, Adrienne, for the suggestion but that isn't what I want. What
I want is something like this:

Dog
Animal Camel
Horse

Parrot
Bird Pigeon
Crow

Soccer
Sport Tennis
Golf

Piano
Music Guitar
Flute

MacOS
OS Windows
Linux

Note that I have left a blank line in between for clarity. In other
words, dog, camel & horse are animals; thus they have been grouped
together corresponding to Animal. Parrot, pigeon & crow are birds;
thus they have been grouped together corresponding to Bird. Soccer
tennis & golf are sports; thus they are grouped together corresponding
to Sport. Piano, guitar & flute are musical instruments; hence they
have been grouped together corresponding to Music. MacOS, Windows &
Linux are operating systems; hence they have been grouped together
corresponding to OS.

To some extent, I have got it with the following code:

<table border=1>
<tr>
<th>TEACHER</th>
<th>CLASS</th>
</tr>
<%
Dim strOld
Do Until(objRS.EOF)
If(objRS("TeacherName")<>strOld) Then
%>
<tr>
<td><%= objRS("TeacherName") %></td>
<%
Else
%>
<td class="noborder">&nbsp;</td>
<%
End If
%>
<td>
<ul>
<li><%= objRS("ClassName") %></li>
</ul>
</td>
</tr>
<%
strOld=objRS("TeacherName")
objRS.MoveNext
Loop
%>
</table>

But the problem is in displaying Animal, Bird, Sport, Music & OS.
Currently the above code would display the following HTML table in the
browser:

Animal Dog
Camel
Horse

Bird Parrot
Pigeon
Crow

Sport Soccer
Tennis
Golf

Music Piano
Guitar
Flute

OS MacOS
Windows
Linux

i.e. Animal resides in the 1st cell under the 1st column but it should
be in the 2nd cell under the 1 column. Currently 2nd & 3rd cells under
the 1st column remain empty but the 1st & the 3rd cell should remain
rempty under the 1st column. The no. of cells in the 2nd column that
the items in a particular group occupy, the group name should occupy
the middle cell. For e.g. Animal has 3 items; hence the text Animal
should occupy the 2nd cell.

Any idea how do I alter the above ASP code to do this?

Thanks once again,

Regards,

RON