Stuck with a bug that I can't beat
runs from
Sub Step2_CreateReportSheetWithConn()
Dim ws_from$, ws_to$, group_name$
ws_from = "Data"
ws_to = "Result"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName& ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
conn.Open
Dim aggQuery$
aggQuery = testQ()
Set rs = CreateObject("ADODB.Recordset")
ThisWorkbook.Sheets(ws_from).Range("A:B,J:L").NumberFormat = "0"
rs.Open aggQuery, conn, 1, 3 ' Run Time error '-2147217900(80040e14)' with no comments
If Not rs.EOF Then
ThisWorkbook.Sheets(ws_to).Cells(6, 1).CopyFromRecordset rs
rs.Close
End If
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
Function testQuery()
''' Returns result query from parts combine '''
q_1 = _
"SELECT [seg], [inn], SUM([kg]) " & _
"FROM [Данные$] " & _
"GROUP BY [seg], [inn] "
q_2 = _
"SELECT [seg], COUNT([inn]), SUM([kg]) " & _
"FROM [Данные$] " & _
"GROUP BY [seg] "
testQuery = _
"SELECT " & _
"c.[seg], c.[sku]," & _
"COUNT(c.[inn]) / COUNT(b.[inn]) as DistrNV, " & _
"SUM(a.[kg]) / SUM(b.[kg]) as DistrV, " & _
"FROM [Данные$] AS c " & _
"LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
"LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _
"GROUP BY c.[seg], c.[sku]"
End Function
As I see it, here is the problem place:
"LEFT JOIN (" & q_1 & ") AS a ON c.[inn] = a.inn AND c.[seg] = a.seg " & _
"LEFT JOIN (" & q_2 & ") AS b ON c.[seg] = b.seg " & _
If I remove 1 line of 2, there will be no error.
I tried swapping keys, swapping "JOIN" rows. Other parts of the request are successfully completed, but there is some kind of trouble at this point.
The syntax for two joins is:
([Данные$] AS c LEFT JOIN q_1 AS a ON c.inn=a.inn AND c.inn=a.inn) LEFT JOIN q_2 AS b ON c.seg= b.seg;
Check if parentheses are required.