Nested queries

[Steve S]

this query (qryX) is a stored query.

SELECT Fees.[SSheet]
FROM Fees INNER JOIN Entries ON Fees.FeeID = Entries.FeeID
WHERE (((Fees.ContestID)=49) AND ((Entries.TwirlerID)=1697))
GROUP BY Fees.[SSheet]
HAVING (((Fees.[SSheet])>0));

How do Nest the SQL code above (qryX) within the SQL string below?

strSQL = "SELECT First(""SS "" & [SS Name]) AS SSName " _
& "FROM qryX INNER JOIN SSheets ON qryX.[SSheet] = SSheets.[SS
ID] " _
& "WHERE ((ScoreSheets.Org) = 1) " _
& "GROUP BY ScoreSheets.SortKey;"

As suggested in various posts to this forum I had to break out qryX because
I was getting the error " Join not supported". I do not want to store either
query as a saved query since they are both single use procedures.

thanks for any and all suggestions

 

[Stefan Hoffmann]

hi Steve,

How do Nest the SQL code above (qryX) within the SQL string below?

strSQL = "SELECT First(""SS "" & [SS Name]) AS SSName " _
& "FROM qryX INNER JOIN SSheets ON qryX.[SSheet] = SSheets.[SS
ID] " _
& "WHERE ((ScoreSheets.Org) = 1) " _
& "GROUP BY ScoreSheets.SortKey;"

Simply replace

.. FROM qryX ..

with

.. FROM (derived table) qrx ..

btw, First() and Last() may provide unpredictable results. In mostly all
cases use Min() and Max() instead.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

with

.. FROM (derived table) qrx ..

This obviously means: paste your queries SQL statement with out the
terminating semi-colon into the parenthesis.


mfG
--> stefan <--

 

[Steve S]

Thank you Stefan,

Worked like a charm
--
Steve S

hi Steve,

How do Nest the SQL code above (qryX) within the SQL string below?

strSQL = "SELECT First(""SS "" & [SS Name]) AS SSName " _
& "FROM qryX INNER JOIN SSheets ON qryX.[SSheet] = SSheets.[SS
ID] " _
& "WHERE ((ScoreSheets.Org) = 1) " _
& "GROUP BY ScoreSheets.SortKey;"

Simply replace

.. FROM qryX ..

with

.. FROM (derived table) qrx ..

btw, First() and Last() may provide unpredictable results. In mostly all
cases use Min() and Max() instead.


mfG
--> stefan <--
.