Syntax of including one SQL query within the other

  • Guys,
    What's the write VBA syntax to include one query within the other?

    Code
    Query1 = "SELECT Count([AAA]) AS [CountOfAAA], Sum([BBB1]) AS [SumOfBBB1] FROM [Sheet1$] " & _
                      "GROUP BY [BBB], [CCC], [DDD], [EEE], [FFF], [JJJ], [KKK] " & _
                      "HAVING ((([FFF])='YES') AND (([JJJ])='No') AND (([KKK])='Active'))
        Set Query1_rs = cn.Execute(Query1)
       
        strQuery = "SELECT Sum([CountOfAAA]) AS [SumOfCountOfAAA], Sum([SumOfBBB1]) AS [SumOfSumOfBBB1] FROM Query1_rs"
        Set rs = cn.Execute(strQuery)


    The second query here does not work, what I made wrong?

  • Re: Syntax of including one SQL query within the other


    Why query a query? Why not just perform the 2nd query to begin with?

  • Re: Syntax of including one SQL query within the other


    To all it may concern , here is the solution that I found:

    Code
    strQuery = "SELECT Sum(MyQuery.[CountOfAAA]) AS [SumOfCountOfAAA], Sum(MyQuery.[BBB1]) AS [SumOfSumOfBBB1] " & _
                    "FROM " & "(SELECT Count([AAA]) AS [CountOfAAA], Sum([BBB1]) AS [SumOfBBB1] " & _
                    "FROM [Sheet1$] " & _
                    "GROUP BY [BBB], [CCC], [DDD], [EEE], [FFF], [JJJ], [KKK] " & _
                    "HAVING ((([FFF])='YES') AND (([JJJ])='No') AND (([KKK])='Active'))) as MyQuery"
    
    
    Set rs = cn.Execute(strQuery)
  • Re: Syntax of including one SQL query within the other


    It's called nested queries:

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!