table display of sql qry

[MtnDew55]

Hi, I'm trying to display the following sql query on an html page:

strQuery = "SELECT tblStudents.LName, tblStudents.FName, tblStudents.SSN, tblStudents.Rate, tblStudents.Rank, tblStudents.OnBdDate, tblStudents.Dept, tblStudents.School, tblStudents.Clerk, tblEvents.EventNum, tblEvents.EventDate, tblEvents.EventCode, tblEventCode.ABBRNM, tblEventCode.NM, tblEvents.Notes, tblEventCode.holdType FROM tblEventCode INNER JOIN (tblStudents INNER JOIN tblEvents ON tblStudents.PKN = tblEvents.PKN) ON tblEventCode.EventCode = tblEvents.EventCode"

(it is all in one line which is you don't see any ampersands, etc)

Here is my problem, The sql query works, in a table I can display everything I ask for. My problem is that I want to display just the name once, then each related event. Right now it prints the event with the name each time.

So basically I need it to look like this:

Name
event 1
event 2
event N
Name
event etc

Can anyone give me an idea or even a place to look?

Thanks

[Psyrus]

Can't you use a GROUP BY in your SQL statement?

"GROUP BY tblStudents.LName"


Chris

[MtnDew55]

Oh sure. Done that ASC. Doesnt help with selecting the associated event.
Seems my main problem is in laying it all out in an table format.
I have done this:

<TABLE Border="1">
<TR>
<TH align="left">
Last Name
</TH>
<td><br></td>
<TH align="left">
First name
</TH>
<td><br></td>
<TH align="left">
SSN
</TH>
<td><br></td>
<TH align="left">
Rate
</TH>
<td><br></td>
<TH align="left">
Rank
</TH>
<td><br></td>
<TH align="left">
STASS Date
</TH>
<td><br></td>
<TH align="left">
Department
</TH>
<td><br></td>
<TH align="left">
School
</TH>
<td><br></td>
<TH align="left">
Clerk
</TH>
</TR>

<%

'Iterate through the recordset, pull
'out the required data, and insert it
'into an HTML table.
Do While Not rs.EOF
%>

<TR>
<TD><% =rs("LName") %></TD>
<td width="15"><br></td>
<TD><% =rs("FName") %></TD>
<td width="15"><br></td>
<TD><% =rs("SSN") %></TD>
<td width="15"><br></td>
<TD><% =rs("Rate") %></TD>
<td width="15"><br></td>
<TD><% =rs("Rank") %></TD>
<td width="15"><br></td>
<TD><% =rs("OnBdDate") %></TD>
<td width="15"><br></td>
<TD><% =rs("Dept") %></TD>
<td width="15"><br></td>
<TD><% =rs("School") %></TD>
<td width="15"><br></td>
<TD><% =rs("Clerk") %></TD>
<td width="15"><br></td>
</TR>

<TR><TD width="100%">

<TH align="left">
Event Date
</TH>


<TH align="left">
Event Code
</TH>


<TH align="left">
Notes
</TH>


<TH align="left">
ABBRNM
</TH>


<TH align="left">
NM
</TH>
</TR>
<%

'If rs.Fields.Item("PKN") = rst.Fields.Item("PKN") Then

Do While rs.Fields.Item("PKN") = rst.Fields.Item("PKN")
%>
<TR>
<TD><% =rst("EventDate") %></TD>
<TD><% =rst("EventCode") %></TD>
<TD><% =rst("Notes") %></TD>
<TD><% =rst("ABBRNM") %></TD>
<TD><% =rst("NM") %></TD>
</TR>


<%
'End If
rst.MoveNext
Loop

%>

<%
rs.MoveNext
Loop
'Close the recordset.
rst.close
rs.Close


'And close the database connection.
cnnDB.Close

%>

</TABLE>

I know I'm doing something wrong, but I dont know what. I know I need to read in a name, then read in all associated events, then read in next name, next events and so forth.
You know, this is so much easier in VB and Access, but certainly no less challenging.

[Psyrus]

Here's one error:

<TR><TD width="100%">

You never close this <TD> tag. It could impact the table layout. Are you joining three db tables with your SQL statement and using two separate recordset objects?

Chris

[MtnDew55]

Good Eye my friend, many of my errors are usually those types. Bad thing about asp and html in notepad is it doesnt point those errors out. I hate using wysiwyg's because of all the extra code it throws in. Wasted space, wasted time. I have to worry about bandwidth until the Navy decides to go from T1 to fiber optic.
Anyway, yes I am working on this from two SQL statements trying to bring together 3 tables:

strQuery = "Select * From tblEvents, tblEventCode"

strQry = "Select * From tblStudents Order By LName ASC"

Look also at my earlier SQL statement from a few days ago where I am trying to use an INNERJOIN. I have had trouble there with syntax errors, which is why I thought I would experiment with the above SQL statements.
Thus the two recordsets, rs and rst.

[Psyrus]

What does this SQL Statement yield?

SELECT
tblStudents.LName, tblStudents.FName, tblStudents.SSN, tblStudents.Rate, tblStudents.Rank, tblStudents.OnBdDate, tblStudents.Dept, tblStudents.School, tblStudents.Clerk, tblEvents.EventNum, tblEvents.EventDate, tblEvents.EventCode, tblEventCode.ABBRNM, tblEventCode.NM, tblEvents.Notes, tblEventCode.holdType
FROM
tblEventCode, tblStudents, tblEvents
WHERE
tblStudents.PKN = tblEvents.PKN
AND
tblEventCode.EventCode = tblEvents.EventCode
ORDER BY
tblStudents.LName ASC


Chris

[MtnDew55]

That produces some good results. Still have the problem of repeating the individuals name for each event for those who have multiple events. It looks nicer if the individual is listed once, then each event associated with him is listed one after the other. Then, the next individuals name, their associated events, etc, etc, etc.

Your SQL code worked like my SQL INNER JOIN. What resolved my problem their was your spotting the absence of the </TD> tag. Now, how do I loop through the recordset so it produces the layout I seek. That is where I'm really stuck.
You know this is great. It really helps to "think" this through like this. Glad this forum is here

[Psyrus]

I'm not sure of the results but try this:

SELECT DISTINCT tblStudents.LName & " " & tblStudents.FName as Name

Just replace the first part of the select statement where you are referring to the first and the last name. You might also have to change the ORDER BY to Name.

Chris

[Psyrus]

I still think the GROUP BY should work. Something like this:

SELECT
tblStudents.LName & " " & tblStudents.FName as Name, tblStudents.SSN, tblStudents.Rate, tblStudents.Rank, tblStudents.OnBdDate, tblStudents.Dept, tblStudents.School, tblStudents.Clerk, tblEvents.EventNum, tblEvents.EventDate, tblEvents.EventCode, tblEventCode.ABBRNM, tblEventCode.NM, tblEvents.Notes, tblEventCode.holdType
FROM
tblEventCode, tblStudents, tblEvents
GROUP BY
Name
HAVING
tblStudents.PKN = tblEvents.PKN
AND
tblEventCode.EventCode = tblEvents.EventCode
ORDER BY
Name ASC

Let me know if this works.


Chris

[MtnDew55]

Chris, Won't be able to try till Monday, let you know then.

[vb_dba]

I faced the similar situation a few weeks back. What I had to eventually wind up doing was in my do loop, check the previous value against the current value, if they were equal then not print that value in my table. You could probably do the same thing, comparing the previous SSN to the current SSN, if they are equal, then don't display the first and last names.

<% Dim PrevSSN 
       PrevSSN='' 
       Do While Not rs.EOF 

      If PrevSSN = '' or PrevSSN <> rs("SSN") then  %>
           <TR> 
           <TD><% =rs("LName") %></TD> 
           <td width="15"><br></td> 
           <TD><% =rs("FName") %></TD> 
           <td width="15"><br></td> 
           <TD><% =rs("SSN") %></TD> 
           <td width="15"><br></td> 
           <TD><% =rs("Rate") %></TD> 
           ETC....
           PrevSSN=rs("SSN")
      Else
           <TR>
           <TD></TD>  <!-- Don't print out the Last Name -->
           <TD></TD>  <!-- Don't print out the First Name -->
           <TD Width="15"><br></td>
           <TD><% =rs("SSN") %></TD>
            ETC...
            PrevSSN=rs("SSN")
      End If
Loop

Unfortunately there is no way to control this with your SQL statement. Using Distinct and Group By will provide you with unique results for each person, but you will still have the same problem with the First Name and Last Name.

Chris