I am using the code below to display a list of results in a table. It is working fine except the ".Write rsName1("grp_code")" section (which is looking for results from another table, is displaying the same value for every record of rsname. How can I get it (rsname1) to display the correct value which matches the record from rsname?

strSQL = "SELECT * FROM Student WHERE syskey = '" & request("syskey") & "'"

stucode = rsname("syskey")
acyear = "03/04"

strSQL1 = "SELECT * FROM stu_grp WHERE stu_code = '" & stucode & "' AND ayrc = '" & acyear & "'"

rsName1.Open strSQL1, adoConn, adOpenDynamic ,adLockReadOnly, adCmdText

While not rsName.EOF

     With Response
           .Write "<tr>"
           .Write "<td>"
           .Write rsName("syskey")

          .Write "</td>"
           .Write "<td>"
           .Write rsName("firstname")
           .Write "</td>"
           .Write "<td>"
           .Write rsName("lastname")
           .Write "</td>"
           .Write "<td>"
           .Write rsName("dob")
           .Write "</td>"
           .Write "<td>"
'*******PROBLEM LINE!!*********
'***DISPLAYS SAME VALUE FOR EVERY RECORD***
           .Write rsName1("grp_code")
           .Write "</td>"
           .Write "</tr>"

     end with
          
'Moves to the next record
rsName.MoveNext

wend
end if

Please help!

the field grp_code does not exist in the table? Perhaps I have made it unclear.

There are two tables one called student and the other called stu_grp. The majority of the results come from student (with the rsName connection), the grp_code comes from stu_grp (with the rsName1 connection). the grp_code field does not exist in the student table.

I would like the correct grp_code value to be outputted next to the correct; syskey, firstname, lastname and dob - being displayed.

Hope thats clearer.