hey folks
i think this should be simple but i just cant do it.
while looping through the records in table1 i want to loop through and display any and all records from table2 that match the record in table1.
like this:

(table1) ID 1 fruits
(table2) ID 1 apple
(table2) ID 1 orange
(table2) ID 1 banana
(table1) ID 2 vegetables
(table2) ID 2 carrots
(table2) ID 2 lettuce
(table2) ID 2 broccoli

any help would be greatly appreciated
thanks,
Jonah

You wanna do a nested loop---> (this is pesudo code)
hope this makes sense...

hello,

i never did get this working, and i ended up skipping it, but now ive got the same problem again.

i tried your suggestion, maybe im not opening the record sets correctly or somehting.

could you give me an example with actual code?

much appreciated,

Jonah

Post the code you have so far.

sure thing...
heres what ive got:

---------- the code starts here----------------------

<TABLE width="215" BORDER=0 cellpadding="0">
<TBODY>
<%
Session.timeout = 5
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\Jsystem.mdb")
sql = "SELECT [codename].[course code], Max([schedule].[day]) AS day‚Ì�Å‘å, Min([prices].[price]) AS minprice FROM (codename INNER JOIN schedule ON [codename].[course code]=[schedule].[course code]) INNER JOIN prices ON [codename].[course code]=[prices].[course code] GROUP BY [codename].[course code] HAVING (((codename.[course code]) Like '*S*'))"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
%>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR>
<TD width="80%"><img src="images/arrowgray.gif" width="11" height="11">
<a href="http://www.hfinet.com/detailspage.asp?code=<% Response.write(rs("course code")) %>"><font size="2px"><span class="textstyle">
<%
sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE (((schedule.[course code]) = '" & rs.[course code] & "'"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open sql2, conn, 3, 3
If rs2.eof Then
rs2.AddNew
End If
%>
<%
On Error Resume Next
rs2.MoveFirst
do while Not rs2.eof
%>
<%=Response.Write(rs2("location"))%>
<%
rs2.MoveNext
loop
%>
<%
rs2.close
set rs2 = nothing
conn2.close
set conn2=nothing
%>

<%=Response.Write(rs("days"))%></span></font>“úŠÔ</a>
�@</TD>
<TD valign="top" nowrap BORDERCOLOR=#c0c0c0 ><span class="textstyle">
�@<font color="#FF0000">\<%=Server.HTMLEncode(formatnumber (rs.Fields("minprice").Value,0))%>�`</font></span>
</TD>
</TR>
<%
rs.MoveNext
loop
%>
<%
rs.close
set rs = nothing
conn.close
set conn=nothing
%>
</TBODY>
</TABLE>

The following 3 lines can be deleted:

set rs2 = nothing ' delete this because you want to reuse rs2
conn2.close ' you dont have a conn2 object - you are using conn - which is ok
set conn2=nothing ' no conn2 object

thanks for the tip,

i went ahead and fixed what you said, but it had no effect.
besides that everything else looks right?

man, looks like i just had a few extra parentheses in the wrong places.
oops,
i wish dreamweaver would color code those things like excel does.

well, thanks for the help,

heres the final code if it helps anybody else:




<TABLE width="215" BORDER=0 cellpadding="0">
<TBODY>
<%
Session.timeout = 5
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\Jsystem.mdb")
sql = "SELECT [codename].[course code], Max([schedule].[day]) AS days, Min([prices].[price]) AS minprice FROM (codename INNER JOIN schedule ON [codename].[course code]=[schedule].[course code]) INNER JOIN prices ON [codename].[course code]=[prices].[course code] GROUP BY [codename].[course code] HAVING (((codename.[course code]) Like '%S%'))"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
%>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR>
<TD width="80%"><img src="images/arrowgray.gif" width="11" height="11">
<a href="http://www.hfinet.com/detailspage.asp?code=<% Response.write(rs("course code")) %>"><font size="2px"><span class="textstyle">
<%
sql2 = "SELECT DISTINCT location FROM schedule WHERE schedule.[course code] = '" & rs("course code") & "'"
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open sql2, conn, 3, 3
%>
<%
On Error Resume Next
rs2.MoveFirst
do while Not rs2.eof
%>
<%=Response.Write(rs2("location"))%>
<%
rs2.MoveNext
loop
%>
<%
rs2.close
%>
<%=Response.Write(rs("days"))%></span></font>“úŠÔ</a>
�@</TD>
<TD valign="top" nowrap BORDERCOLOR=#c0c0c0 ><span class="textstyle">
�@<font color="#FF0000">\<%=Server.HTMLEncode(formatnumber (rs.Fields("minprice").Value,0))%>�`</font></span>
</TD>
</TR>
<%
rs.MoveNext
loop
%>
<%
rs.close
set rs = nothing
conn.close
set conn=nothing
%>
</TBODY>
</TABLE>

What was the error you were receiving ? And highlight the code you changed that resolved your issue.

no error,
it would just hang, as it always does when i have a misspelled field or bad syntax in my WHERE clause.

there were actually a couple of problems in the code:

1: i was using * instead of % as a wildcard in my WHERE clause (although i dont really know if this causes any problems)

2: i forgot i had called a MIN on my "day" field as day‚Ì�Å‘å but then tried to display it as "days" on accident

3: and this was probably the worst, i put 3 open parentheses when there shouldnt have been any, or i should have closed them at least:

sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE (((schedule.[course code]) = '" & rs.[course code] & "'"

should have been:

sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE schedule.[course code]) = '" & rs.[course code] & "'"

Can this be done using a SQL join? I am sure this could take care of your issue..and your recordset will only hold the matching infomation from your SQL query

rhs98, im not sure what you mean.

could you post some example code so i can see what youre talking about?

jonah - thanks for posting the solution. I was wondering what those special characters were. Anyway, you can simply your solution by eliminating the select from location and reducing your resultset by putting the having clause as a where clause instead.