Using Two Recordset and Queries in ASP - please help

Hello,

I have 2 queries, qryDept and qryExams. Because I can not join these queries together so I have to use 2 Record sets in ASP.

The NoStudents deduct from NoExams to get Remain.

It keeps getting errors at the --oRs2.Open strSQL2, Conn.

		<% Dim Dept_ID, strSQL2, oRs2, Remain
		strSQL = "SELECT Dept_ID, NoStudents FROM qryDept; " & _
		strSQL2 = "SELECT Dept_ID, NoExams FROM qryExams " & _
			" Where = qryExams.Dept_ID = qryDept.Dept_ID;"
				
		Set oRs = Server.CreateObject("adodb.RecordSet")
		oRs.Open strSQL, Conn
		Set oRs2 = Server.CreateObject("adodb.RecordSet")
		oRs2.Open strSQL2, Conn
		if not oRs.eof then	%>
				<%	do until oRs.eof %>
			<tr>
				<th><%=oRs("Dept_ID")%></th>
				<th><%=oRs("NoStudents")%></th>
				<% if not oRs2.eof then	%>
				<%	do until oRs2.eof %>
				<th><%=oRs2("NoExams")%></th>
				<% Remain = <%=oRs("NoStudents")%>-<%=oRs2("NoExams")%>%>
				<th><%=Remain%></th>
				<%	oRs2.MoveNext
					loop	%>
		<%	end if	%>
				<%	oRs.MoveNext
					loop	%>
		<%	end if	%>

Code (markup):

Errors: Too few parameters. Expected 1. at Line ---oRs2.Open strSQL2, Conn

Can anyone please help how to fix this code to make it work?

Thanks very much and have a nice day.

 

I'm not sure why you can't join those tables together and I'm not a professional in queries but I'm sure the syntax is wrong.
You cannot write str1="1" & str2="2" and that's pretty much what you did. There should be no "&" between variables.

If you can't use joins, I believe the correct answer would be:

strSQL = "SELECT Dept_ID, NoStudents FROM qryDept, qryExams WHERE qryExams.Dept_ID = qryDept.Dept_ID"

strSQL2 = "SELECT Dept_ID, NoExams FROM qryDept, qryExams WHERE qryExams.Dept_ID = qryDept.Dept_ID"

Code (markup):

and with join I think it should be:

strSQL="SELECT Dept_ID, NoStudents, NoExams FROM qryDept LEFT JOIN qryExams ON qryExams.Dept_ID = qryDept.Dept_ID"

Code (markup):