Using Two Recordset and Queries in ASP - please help

[jennypretty]

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 %>


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.

[Old Pedant]

Where = qryExams.Dept_ID = qryDept.Dept_ID;

Does that REALLY look right to you???

I hope not. You can't have an = directly after the keyword WHERE.

But in any case, this code makes no sense. You are trying to refer to ANOTHER QUERY when you never mention that query in the FROM clause.

And you say "I can not join these queries together..."

WHY can't you??? You surely SHOULD join them together.

Period.

Try this code and see what happens:

<%
strSQL = "SELECT D.Dept_ID, D.NoStudents, E.NoExams " _
& " FROM qryDept AS D, qryExams AS E " _
& " WHERE E.Dept_ID = D.Dept_ID"
Set oRs = Conn.Execute( strSQL )
Do Until oRs.eof
%>
<tr>
<th><%=oRs("Dept_ID")%></th>
<th><%=oRs("NoStudents")%></th>
<th><%=oRs2("NoExams")%></th>
<th><%=oRs("NoStudents") - oRs2("NoExams")%></th>
</tr>
<%
oRs.MoveNext
loop
%>


And if it doesn't do what you want it to do, tell WHY it doesn't.

[jennypretty]

The reason I can't use the join is because I have a complicated query for qryDept.
Here is the qryDept:

SELECT

DISTINCT
tbl_Dept.Dept_ID, tbl_Dept.NAME ,
tbl_Dept.NUM AS N1,

(

SELECT IIf( SUM(tbl_Dept_2.NUM) IS NOT NULL, SUM
(tbl_Dept_2.NUM))

FROM tbl_Dept AS tbl_Dept_2

WHERE tbl_Dept_2.Dept_ID = tbl_Dept.Dept_ID AND
tbl_Dept_2.NAME <> tbl_Dept.NAME

GROUP BY tbl_Dept_2.Dept_ID

) AS SUB_TOTAL, CInt(N1*N1) + IIF( len(SUB_TOTAL) > 0, SUB_TOTAL, 0) AS
FINAL_TOTAL

FROM tbl_Dept LEFT JOIN tbl_Dept AS

tbl_Dept_1 ON tbl_Dept.Dept_ID =
tbl_Dept_1.Dept_ID AND tbl_Dept.Name <>
tbl_Dept_1.Name

So... when using qryExams to join with qryDept, it returns incorrect data.

Thanks.

[Old Pedant]

Well, a lot of things wrong in that stored query. Surprised it works at all. So maybe not surprising you can't join it to the qryDept.

I don't know. Without the DB in front of me, I can't really answer much more.