Yet another SQL question

[kah0563]

I am trying to create a select statement, then create another select statement that will join with the first. Another words I want to create a query with the first select statement, then join another table to the resulting query in the 2nd select statement.

Can you create a Select statement, and attach an alias to the entire select statement (AS SQL1)?

I have to recordsets coded like the following, bombs on the first recordset (select statement). I put the AS [Rpt New Business] after the ORDER BY clause, but must be missing parentheses or something??? Anybody help. Thanks

mrsHNC1.Open "SELECT ([" & Range("MACCT") & "].acct_id AS [Acct No], [" & Range("ACCTS") & "].name AS [Balance Sheet], " & _
"[" & Range("ACCTS") & "].name2 AS [Income Statement], [ZZ CashFlow Type].Description AS AmortizationType, " & _
"PREPAYMT.name AS [Prepay Speed], [" & Range("MACCT") & "].ad_matmon AS [Mat Mo], [" & Range("MACCT") & "].ad_ballofs AS [Ball Adj], " & _
"BASERATE.name AS [Base Rate], [" & Range("ADDTN") & "].nbal_rate1 AS Spread, BASERATE.scen_id as basescen_id, " & _
"[" & Range("ADDTN") & "].strat_id as spreadstrat_id, PREPAYMT.scen_id as prepayscen_id " & _
"FROM ((((" & Range("MACCT") & " LEFT JOIN " & Range("ACCTS") & " ON [" & Range("MACCT") & "].acct_id = [" & Range("ACCTS") & "].acct_id) " & _
"LEFT JOIN [ZZ CashFlow Type] ON [" & Range("MACCT") & "].ad_matmeth = [ZZ CashFlow Type].[Model Code]) " & _
"LEFT JOIN " & Range("ADDTN") & " ON [" & Range("MACCT") & "].acct_id = [" & Range("ADDTN") & "].acct_id) " & _
"LEFT JOIN PREPAYMT ON [" & Range("MACCT") & "].ad_ppspeed = PREPAYMT.pp_id) " & _
"LEFT JOIN BASERATE ON [" & Range("MACCT") & "].base_rate = BASERATE.base_id " & _
"WHERE (((BASERATE.scen_id) = ""0001"") And (([" & Range("ADDTN") & "].strat_id) = ""01"") And ((PREPAYMT.scen_id) = ""0002"")) " & _
"ORDER BY [" & Range("MACCT") & "].acct_id) as [Rpt New Business]", mcnHNC, adOpenDynamic, adLockBatchOptimistic

mrsHNC.Open "SELECT [Rpt New Business].[Base Rate] AS [Pricing Rate], " & _
"[Rpt New Business].Spread AS [Pricing Spread] " & _
"FROM ([Rpt New Business] INNER JOIN [Rpt MV] ON " & _
"[Rpt New Business].[Acct No] = [Rpt MV].[Acct No]) " & _
" INNER JOIN " & Range("ACCTS") & " ON [Rpt New Business].[Acct No] = [" & Range("ACCTS") & "].acct_id " & _
"WHERE ((([" & Range("ACCTS") & "].detail) = -1)) " & _
"ORDER BY [" & Range("ACCTS") & "].group_pos", mcnHNC, adOpenDynamic, adLockOptimistic

 

[PHV]

The syntax is:
SELECT A.Field1, B.Field2, ...
FROM OneTable A INNER JOIN (
SELECT ... FROM ... WHERE ...
) B ON A.SomeKey=B.SomeKey
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244