Subqueries...CODE NEEDED!

[ineedahelp]

I would like some help with the proper way to code a subquery. My strSQL is the first query. It aggregates data for me and selects the top 20. After this is done, I would like to SORT this output on symbol. I would also like to ADD a field to this query that I could run the UPDATE query on to add the date. Can I also programatically change the field name of a query using CAPTION?
Thank you for any help!!

Private Sub comboExcelForVe ndor_AfterUpdat e()
On Error GoTo Err_MyProc
Dim dbs As DAO.Database, qdf As DAO.QueryDef, strSQL, strSQLFinal As String
Set dbs = CurrentDb
Dim strTableName, strClient, strExcelName, strDate As String
Dim myPos

strTableName = Forms!frmDailyC lient!comboExce lforVendor
myPos = InStr(1, strTableName, "_", vbTextCompare) - 1
strExcelName = Left(strTableNa me, myPos)
strDate = Right(strTableN ame, 7)
Set qdf = dbs.QueryDefs(" qryVendorReques t")

strSQL = "SELECT TOP 20 " & strTableName & ".Symbol, " & strTableName & ".Cusip, Sum(" & strTableName & ".TotalQty) AS SumOfTotalQty " & _
"FROM " & strTableName & _
" GROUP BY " & strTableName & ".Symbol, " & strTableName & ".Cusip, " & strTableName & ".ML_DailyR ate " & _
"HAVING (((" & strTableName & ".ML_DailyR ate) < 4.5 Or (" & strTableName & ".ML_DailyR ate) = 4.5)) " & _
"ORDER BY Sum(" & strTableName & ".ML_SMV);"
qdf.SQL = strSQL
strSQLFinal = "SELECT strSQL.Symbol, strSQL.Cusip, strSQL.SumOfTot alQty " & _
"FROM strSQL " & _
"ORDER BY strSQL.Symbol;"

qdf.SQL = strSQLFinal
Debug.Print strSQLFinal
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "qryVendorReque st", "c:\documen ts and settings\" & mUser & "\My Documents\COMPA NY NAME\" & strExcelName & "\" & strExcelName & " Rate Request" & strDate & ".xls", True
MsgBox ("File successfully exported")
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
Exit Sub

Exit_MyProc:
qdf.Close
Set dbs = Nothing
Set qdf = Nothing
Exit Sub

Err_MyProc:
Resume Exit_MyProc

End Sub

[NeoPa]

A subquery in SQL must be one that shows records (no action queries).
It is done by enclosing an ordinary query (SELECT ... FROM ...) inside parentheses ().
If you have a subquery in the FROM clause, it is usual to rename it (SELECT ...) AS NewName.
Fields that are returned from a subquery can then be referred to in the containing query (the main one) as NewName.Field where Field is the precomputed result produced by the subquery.
For example - the two SQL strings below are equivalent :

SELECT MySubQuery.* FROM (SELECT * FROM myTable) AS MySubQuery
SELECT * FROM myTable