Display DB Records Differently

Display DB Records Differently

am 13.05.2007 14:15:01 von 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:





















TEACHER NAME CLASS NAME
Peter Class-3
Peter Class-7
Peter Class-9


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






<%
Do Until(objRS.EOF)
%>




<%
objRS.MoveNext
Loop
%>

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

TEACHER NAME CLASS NAME
<%= objRS("TeacherName") %> <%= objRS("ClassName") %>










TEACHER NAME CLASS NAME
Peter

  • Class-3

  • Class-7

  • Class-9




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

Re: Display DB Records Differently

am 13.05.2007 18:54:27 von Adrienne Boswell

Gazing into my crystal ball I observed rn5a@rediffmail.com writing in
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:
>
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
TEACHER NAMECLASS NAME
PeterClass-3
PeterClass-7
PeterClass-9

>
> 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
> %>
>
>
>
>
>
>
><%
> Do Until(objRS.EOF)
> %>
>
>
>
>
><%
> objRS.MoveNext
> Loop
> %>
>
> But I want the source code of the HTML table to look something like
> this:
>
>
TEACHER NAMECLASS NAME
<%= objRS("TeacherName") %><%= objRS("ClassName") %>

>
>
>
>
>
>
>
>
>
>
TEACHER NAMECLASS NAME
Peter
>

    >
  • Class-3

  • >
  • Class-7

  • >
  • Class-9

  • >

>

>
> 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 Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Display DB Records Differently

am 13.05.2007 21:59:46 von rn5a

On May 13, 9:54 pm, Adrienne Boswell wrote:
> Gazing into my crystal ball I observed r...@rediffmail.com writing innews: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:
>
> >


> >
> >
> >
> >
>
> >
> >
> >
> >
>
> >
> >
> >
> >
>
> >
> >
> >
> >
> >
TEACHER NAMECLASS NAME
PeterClass-3
PeterClass-7
PeterClass-9

>
> > 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
> > %>
>
> >
> >
> >
> >
> >
> ><%
> > Do Until(objRS.EOF)
> > %>
> >
> >
> >
> >
> ><%
> > objRS.MoveNext
> > Loop
> > %>
>
> > But I want the source code of the HTML table to look something like
> > this:
>
> >
TEACHER NAMECLASS NAME
<%= objRS("TeacherName") %><%= objRS("ClassName") %>

> >
> >
> >
> >
>
> >
> >
> >
> >
> >
TEACHER NAMECLASS NAME
Peter
> >

    > >
  • Class-3

  • > >
  • Class-7

  • > >
  • Class-9

  • > >

> >

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

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

Re: Display DB Records Differently

am 15.05.2007 09:07:33 von Adrienne Boswell

Gazing into my crystal ball I observed rn5a@rediffmail.com writing in
news:1179086386.270713.26530@l77g2000hsb.googlegroups.com:

> On May 13, 9:54 pm, Adrienne Boswell wrote:
>> Gazing into my crystal ball I observed r...@rediffmail.com writing
>> innews: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:
>>
>> >


>> >
>> >
>> >
>> >
>>
>> >
>> >
>> >
>> >
>>
>> >
>> >
>> >
>> >
>>
>> >
>> >
>> >
>> >
>> >
TEACHER NAMECLASS NAME
PeterClass-3
PeterClass-7
PeterClass-9

>>
>> > 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
>> > %>
>>
>> >
>> >
>> >
>> >
>> >
>> ><%
>> > Do Until(objRS.EOF)
>> > %>
>> >
>> >
>> >
>> >
>> ><%
>> > objRS.MoveNext
>> > Loop
>> > %>
>>
>> > But I want the source code of the HTML table to look something like
>> > this:
>>
>> >
TEACHER NAMECLASS NAME
<%= objRS("TeacherName") %><%= objRS("ClassName") %>

>> >
>> >
>> >
>> >
>>
>> >
>> >
>> >
>> >
>> >
TEACHER NAMECLASS NAME
Peter
>> >

    >> >
  • Class-3

  • >> >
  • Class-7

  • >> >
  • Class-9

  • >> >

>> >

>>
>> > 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 -
>
> 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 "
testthingy: " & tempthingy
if trim(tempthingy) = trim(testarr(i)) then
else
response.write "
" & 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 Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Display DB Records Differently

am 16.05.2007 06:11:54 von rn5a

On May 15, 12:07 pm, Adrienne Boswell wrote:
> Gazing into my crystal ball I observed r...@rediffmail.com writing innews:1179086386.270713.26530@l77g2000hsb.googlegroups.com:
>
>
>
>
>
> > On May 13, 9:54 pm, Adrienne Boswell wrote:
> >> Gazing into my crystal ball I observed r...@rediffmail.com writing
> >> innews: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:
>
> >> >


> >> >
> >> >
> >> >
> >> >
>
> >> >
> >> >
> >> >
> >> >
>
> >> >
> >> >
> >> >
> >> >
>
> >> >
> >> >
> >> >
> >> >
> >> >
TEACHER NAMECLASS NAME
PeterClass-3
PeterClass-7
PeterClass-9

>
> >> > 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
> >> > %>
>
> >> >
> >> >
> >> >
> >> >
> >> >
> >> ><%
> >> > Do Until(objRS.EOF)
> >> > %>
> >> >
> >> >
> >> >
> >> >
> >> ><%
> >> > objRS.MoveNext
> >> > Loop
> >> > %>
>
> >> > But I want the source code of the HTML table to look something like
> >> > this:
>
> >> >
TEACHER NAMECLASS NAME
<%= objRS("TeacherName") %><%= objRS("ClassName") %>

> >> >
> >> >
> >> >
> >> >
>
> >> >
> >> >
> >> >
> >> >
> >> >
TEACHER NAMECLASS NAME
Peter
> >> >

    > >> >
  • Class-3

  • > >> >
  • Class-7

  • > >> >
  • Class-9

  • > >> >

> >> >

>
> >> > 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 -
>
> > 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 "
testthingy: " & tempthingy
> if trim(tempthingy) = trim(testarr(i)) then
> else
> response.write "
" & 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:






<%
Dim strOld
Do Until(objRS.EOF)
If(objRS("TeacherName")<>strOld) Then
%>


<%
Else
%>

<%
End If
%>


<%
strOld=objRS("TeacherName")
objRS.MoveNext
Loop
%>
TEACHER CLASS
<%= objRS("TeacherName") %>  

  • <%= objRS("ClassName") %>




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