Row source is SQL on SQL (subquery problem)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya

I have a report based on an SQL query (lets say SQL1). Within the report I have a chart based on another SQL query (lets say SQL2). However SQL2 is based solely on SQL1

I can't get SQL2 to all be referenced in SQL without referring to SQL1 as a saved query. I want SQL1 to be built through code, hence do not want it to be a saved query

Can anyone help me (or tell me if it is possible) to incorporate the code of SQL1 into SQL2 without referencing SQL1 as a saved query

Here is the code for SQL1

SELECT tblQuestions.SectnID, tblSections.SectnText, tblQuestions.QstnID, IIf([QstnLvl2] Is Null,[QstnLvl1],[QstnLvl1] & " " & [QstnLvl2] & ")") AS QstnNo, tblQuestions.QstnText, tblResponsesList.RspnsVal, qryStatResponses.StatRspns, Count(qryStatResponses.StatRspns) AS CountOfStatRspn
FROM tblSections INNER JOIN ((tblQuestions INNER JOIN qryStatResponses ON tblQuestions.QstnID = qryStatResponses.QstnID) INNER JOIN tblResponsesList ON (qryStatResponses.QstnID = tblResponsesList.QstnID) AND (qryStatResponses.StatRspns = tblResponsesList.Rspns)) ON tblSections.SectnID = tblQuestions.SectnI
GROUP BY tblQuestions.SectnID, tblSections.SectnText, tblQuestions.QstnID, IIf([QstnLvl2] Is Null,[QstnLvl1],[QstnLvl1] & " " & [QstnLvl2] & ")"), tblQuestions.QstnText, tblResponsesList.RspnsVal, qryStatResponses.StatRspns

Here is the code for SQL2

SELECT SQL1.StatRspns, SQL1.CountOfStatRspns FROM SQL1 GROUP BY SQL1.StatRspns, SQL1.CountOfStatRspns, SQL1.RspnsVal ORDER BY SQL1.RspnsVal DESC

Thanks so much for any advice

Basil
 
Basil:

You might try creating an empty query definition in access and then updating
that using either DAO (faster by far) or ADOX to incorporate the sql from
SQL 1 before you run the report. Then your SQL2 can reference SQL1,
provided the field definitions don't change. If you need to change the row
source for a chart at run time (which is required if fields change), this is
a complex issue to do and still requires SQL1 to be saved, but you can do it
as shown on our web site in the Code and Design tips area, looking at the
current tip.

HTH
 
Back
Top