SQL Query in VBA

[tigor]

Hello Everybody!

I'm trying to get collect data from two excel sheet with OLEDB and sql query with subquery but couldnt.
I got error messaage:
At most one record can be returned by this subquery (Run-time error :80004005)

Please help me, I was trying to find solution on internet but couldnt.

Sub Makró1()
'
' Makró1 Makró
'
 Dim myConnection As String
    Dim RS As ADODB.Recordset
    Dim mySQL As String
    Dim strPath As String
    Dim wsMain As Worksheet
    Debug.Print Now
    Set wsMain = Worksheets("Report")
    Application.ScreenUpdating = False
    strPath = ActiveWorkbook.FullName
    myConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=" & strPath & ";Extended Properties=Excel 12.0"
                    
            mySQL = "SELECT [T2$].[A], " & _
                    "[T2$].[B1], " & _
                    "[T1$]., " & _
                    "(select SUM([T2$].[B1]) FROM [T2$] GROUP BY [T2$].[A], [T2$].[B1]) " & _
                    "FROM [T2$] inner JOIN [T1$] ON [T2$].[A] = [T1$].[A]  where [t1$]. <> 0"
              
        Set RS = New ADODB.Recordset
        RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
        
        wsMain.Range("A2").CopyFromRecordset RS
    RS.Close
    Set RS = Nothing
    Application.ScreenUpdating = True
    Debug.Print Now
'
End Sub

 

[Joe4]

I can do this in a series of two queries, but am having issues doing it all in one.

If we had a query to do the SUM part, and called it "Q1", and the query looked like this:

SELECT T2.A, Sum(T2.B1) AS SumB1
FROM T2
GROUP BY T2.A;

Then we could do another query structured like this (which would return what you want):

SELECT T2.A, T2.B1, T1.B, Q1.SumB1
FROM (T2 INNER JOIN T1 ON T2.A = T1.A) INNER JOIN Q1 ON T1.A = Q1.A
WHERE (((T1.B)<>0));

(Note that I left the "$" and brackets off of the table names to avoid the issues you were having posting).

 

[tigor]

I can do this in a series of two queries, but am having issues doing it all in one.

If we had a query to do the SUM part, and called it "Q1", and the query looked like this:

SELECT T2.A, Sum(T2.B1) AS SumB1
FROM T2
GROUP BY T2.A;

Then we could do another query structured like this (which would return what you want):

SELECT T2.A, T2.B1, T1.B, Q1.SumB1
FROM (T2 INNER JOIN T1 ON T2.A = T1.A) INNER JOIN Q1 ON T1.A = Q1.A
WHERE (((T1.B)<>0));

(Note that I left the "$" and brackets off of the table names to avoid the issues you were having posting).

thanks for the info.
yes, this workaround is known but why I should use this ?
why IT doesn give a solution for an error???? I mean excel, vba,sql developers.
I'm sad.

 

[Joe4]

The issue is that you are not "linking" the totals subquery in to the tables in the FROM clause.
You need to build that relationship between that and the other objects in the query, and you have not done that.
If you just try placing it in the SELECT portion of your query (like you did), there is nothing linking the totals for "A" to the "A" records in the other query.

I am pretty sure that there is a way to do it, but it can get a little tricky, and it has been a few years since I wrote a lot of complex SQL queries.