Loop needed and a count i think, unsure how to proceed

[rreynolds24]

I am trying to display a list of students, their subjects, and some data for each subject. It kinda works, except it repeats the pupils name for each subject:

http://www.peninsulafm.com/atts.JPG

I want it to show the pupils name just once in the left column, then, in the one row, show each of the students subjects, and corresponding data

(so it would look something like this)


http://www.peninsulafm.com/atts2.JPG

Here is my query from the database:

Query="SELECT pupils.PupilID, pupils.Surname, pupils.Forename, pupils.Form, subjects.subjectName, reports.attitud, pupils.attcomment"
Query=Query & " FROM (subjects RIGHT JOIN sets ON subjects.subjectID = sets.setsubject) RIGHT JOIN (pupils LEFT JOIN reports ON pupils.PupilID = reports.pupilID) ON sets.setid = reports.setID"
Query=Query & " WHERE form like '%" & request("form") &"%'"
Query=Query & " ORDER by surname"

and here is the table code

<TABLE border="1" cellspacing="1" style="border-collapse: collapse" width="50%">
<TR><font size="2">
<TD width="10%" align="center"><b>Pupil</b></TD>
<TD width="5%"><b>Subjects</b></TD>
<TD width="3%"><b>Attitudinals</b></TD>
<TD width="25"><b>Attitudinal Comment</b></td>
</font></TR>
<%
Do While Not RSlist.EOF
%>
<TR>
<TD width="15%" valign="top">
<%=RSlist("Forename")%>&nbsp;<%=RSlist("Surname")%><br>
<%=RSlist("Form")%></TD>
<TD width="5%" valign="top">
<% ' Needed is some code to iterate and display each subject without showing the name again
response.write(RSlist("subjectname") & "<br />")
%>
</TD>
<TD width="3%" valign="top" align="center">
<%
' write the attitudinal and a line break
response.Write(RSlist("attitud") & "<br />")
%>
</TD>
<TD width="25%" valign="top"><%=RSlist("attcomment")%>&nbsp;</TD>
</TR>
<%
RSlist.Movenext
Loop
%>
</TABLE>

I know I need to somehow do a count of how many subjects per student and then iterate through that, but I dont really know how to go about it.

Sorry for the long post, but if anyone can help me please it would be much appreciated. Thanks :)

[Spudhead]

Your SQL looks odd to me, but I'm no expert so I'll assume it's churning out the right data. What you need is to know, each time through the loop, is whether or not you're on a new student or not.

So:

<%

strCurrentStudent = ""

Do While Not RSlist.EOF

response.write("<tr>")

strThisStudentName = RSlist("Forename") & "&nbsp;" & RSlist("Surname")

if strThisStudentName <> strCurrentStudent then

'we have a new student - write their name
response.write("<td width=""15%"" valign=""top"">" & strThisStudentName & "<br/>" & RSlist("Form") & "</td>")

'now set the current student to this student
strCurrentStudent = strThisStudentName

else

'still on the current student - write an empty cell
response.write("<td width=""15%"" valign=""top"">&nbsp;</td>")

end if

response.write("<td width=""5%"" valign=""top"" align=""center"">" & RSlist("subjectname") & "</td>")

response.write("<td width=""3%"" valign=""top"" align=""center"">" & RSlist("attitud") & "</td>")

response.write("<td width=""25%"" valign=""top"" align=""center"">" & RSlist("attcomment") & "</td>")

response.write("</tr>")

RSlist.Movenext
loop

%>


That make sense?

[rreynolds24]

That makes sense, and works well, thanks. However, it seems that when there are 2 surnames in the same class it jumbles them up..

http://www.peninsulafm.com/att3.JPG

I cant see why that would be.. my sql maybe?

(I took out the formclass bit)

[hinch]

order by surname,firstname

should do it i think

[rreynolds24]

thanks, that does the trick :)

[rreynolds24]

I have been asked if i can make the table differently, with just a row with the subject names and then a row with the grades underneath. As there are different numbers of subjects for each student, Im not really sure how to go about re-designing the table so it looks something like this:


Bob Subject1 Subject2 Subject3
A B C
Jane Subject1 Subject2 Subject3 Subject4
C A B D