Joining 2 Queries

[Little_Man22]

I need to use the value that is retrieved from one query in another query. Here's the first query:

query1 = "SELECT * FROM AllBands WHERE RateInfoID=2 AND Plan="&Form_Plan&" AND MinAmount <= "&Form_Amount&" AND MaxAmount > "&Form_Amount&""

query2 = "SELECT Rate FROM Can_Life WHERE RateInfoID=2 AND Sex="&Form_Sex&" AND Class=6 AND Age="&Form_Age&" AND Plan="&Form_Plan&" AND Renewal=0 AND Band="THE VALUE RETURNED FROM QUERY 1""

How do I do this?

Thanks in advance,
-Ryan.

 

[Alexandre]

Nest your queries: use your query 1 as a criteria for the query2. The resulting SQL should look like:

SELECT Rate FROM Can_Life
WHERE ((RateInfoID=2)
   AND (Sex="&Form_Sex&")
   AND (Class=6)
   AND (Age="&Form_Age&")
   AND (Plan="&Form_Plan&")
   AND (Renewal=0)
   AND Band IN(SELECT Band FROM AllBands
               WHERE ((RateInfoID=2)
                  AND (Plan="&Form_Plan&")
                  AND (MinAmount <= "&Form_Amount&")
                  AND (MaxAmount > "&Form_Amount&""))))

Alex

[This message has been edited by Alexandre (edited 03-04-2002).]

 

[Little_Man22]

It won't work Alex...here's what I have codewise:

<%
Dim dbBand
Dim rsBand
Dim oneRec
Dim Form_Amount
Dim Form_Plan
Dim Form_Sex
Dim Form_Age

Form_Amount = Request.Form("Amount")
Form_Plan = Request.Form("Plan")
Form_Sex = Request.Form("Sex")
Form_Age = Request.Form("Age")

Set dbBand = Server.CreateObject("ADODB.Connection")
dbBand.Open("rates_131708")

oneRec = "SELECT Rate FROM Can_Life WHERE ((RateInfoID=2) AND (Sex="&Form_Sex&") AND (Class=6) AND (Age="&Form_Age&") AND (Plan="&Form_Plan&") AND (Renewal=0) AND Band IN (SELECT Band FROM AllBands WHERE ((RateInfoID=2) AND (Plan="&Form_Plan&") AND (MinAmount <= "&Form_Amount&") AND (MaxAmount > "&Form_Amount&"))))"

Set rsBand = Server.CreateObject("ADODB.Recordset")

rsBand.Open oneRec, dbBand

Do While Not rsBand.EOF

Response.Write "<li><p>" & rsBand("Rate")*(Form_Amount/100)+40 & "</p></li>"
rsBand.MoveNext
Loop
If rsBand.BOF Then
Response.Write "<p>Canada Life does not offer this policy.</p>" & VBCrLf
End If

rsBand.Close
dbBand.Close
%>

 

[Alexandre]

Little man,

What error exactly do you get?
I have hand-written the above SQL statement, since I do not have the underlying structure to test it or build it from the QBE grid. It si likely that there is a syntax error somewhere.
Build the nested queries from the QBE grid, then test the result, and copy and paste the SQL into your code when OK.

And do not forget to put any string value between single quotes when in VBA.

Alex

[This message has been edited by Alexandre (edited 03-04-2002).]