news:1179058500.940097.129970@p77g2000hsh.googlegroups.com:

> 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
>
>

> Gazing into my crystal ball I observed r***@rediffmail.com writing innews:1179058500.940097.129***@p77g2000hsh.googlegroups.com:
>
>
>
>
>
> > 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.
>
> --
> 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 -