By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Subqueries...CODE NEEDED!

P: 98
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 comboExcelForVendor_AfterUpdate()
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!frmDailyClient!comboExcelforVendor
myPos = InStr(1, strTableName, "_", vbTextCompare) - 1
strExcelName = Left(strTableName, myPos)
strDate = Right(strTableName, 7)
Set qdf = dbs.QueryDefs("qryVendorRequest")

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

qdf.SQL = strSQLFinal
Debug.Print strSQLFinal
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryVendorRequest", "c:\documents and settings\" & mUser & "\My Documents\COMPANY 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
Nov 15 '06 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,492
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT MySubQuery.* FROM (SELECT * FROM myTable) AS MySubQuery
  2. SELECT * FROM myTable
Nov 16 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.