CODEWITHSUNDEEP
sqlvbaexceladodb
atame
atame

Reputation: 521

Excel VBA ADODB SQL sum if data matches

This is the query I am currently using to generate my SQL string, which is currently getting the correct information.

Dim colArray As Variant: colArray = Array(7,9,6,8,10,11,15,25)                               

szSQL = "SELECT 
          sWIR.[F" & Join(colArray, "], sWIR.[F") & "], sCodes.[F7]
          FROM [" & SourceWIR & "] sWIR INNER JOIN [" & SourceCODES & "] AS sCodes ON sWIR.[F5] = sCodes.[F6] 
          WHERE sWIR.[F1] =""" & Address & """ 
          ORDER BY sCodes.[F7], sWIR.[F7];"

What I need it to include is if the data in columns 7, 9, 6 are equal then sum column 10

Below is the full code, I am using for the ADODB Connection

Dim szConnect As String, szSQL As String
Dim SourceFileName As String: SourceFileName = Left(wb.Name, Len(wb.Name) - 5)
Dim SourceFile As String: SourceFile = wb.FullName
Dim rsCon As New ADODB.Connection
Dim rsData As New ADODB.Recordset
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & SourceFile & ";" & "Extended Properties=""Excel 12.0;HDR=No"";"
rsCon.Open szConnect

Dim LastRow As Long: LastRow = wb.WIR.Cells(Rows.Count, "A").End(xlUp).Row
Dim LastCol As Long: LastCol = wb.WIR.Cells(1, wb.WIR.Columns.Count).End(xlToLeft).Column

Dim SourceWIR As String: SourceWIR = wb.WIR.Name & "$"
Dim SourceCODES As String: SourceCODES = wb.CODES.Name & "$"

Dim colArray As Variant: colArray = Array(7,9,6,8,10,11,15,25)

szSQL = "SELECT  sWIR.[F" & Join(colArray, "], sWIR.[F") & "], sCodes.[F7]FROM [" & SourceWIR & "] sWIR INNER JOIN [" & SourceCODES & "] AS sCodes ON sWIR.[F5] = sCodes.[F6] " & _
        "WHERE sWIR.[F1] =""" & Address & """ ORDER BY sCodes.[F7], sWIR.[F7];"

rsData.Open szSQL, rsCon, adOpenKeyset, adLockOptimistic

wb.Sheets("Sheet2").Range("A25").CopyFromRecordset rsData

Below is an image of what the data looks like now, and how the data should look when matches are found.

enter image description here

With help from Parfait, who provided me with the correct string.

After spending hours googling and testing I was able to get the correct string to exclude zero values.

szSQL = SELECT 
             main.[wF7], main.[F9], main.[F6], Max(main.[F8]) As maxF8,
             Sum(main.[F10]) As sumF10, Max(main.[F11]) As maxF11, 
             Max(main.[F15]) As maxF15, Max(main.[F25]) As maxF25, 
             Max(main.[sF7]) As maxsF7 
         FROM (
             SELECT 
                 sWIR.[F7] As wF7, sWIR.[F9], sWIR.[F6], sWIR.[F8], sWIR.[F10],
                 sWIR.[F11], sWIR.[F15], sWIR.[F25], sCodes.[F7] As sF7 
             FROM 
                 [Works Instruction Record$] AS sWIR 
             INNER JOIN 
                 [Codes$] AS sCodes 
             ON 
                 sWIR.[F5] = sCodes.[F6] 
             WHERE 
                 sWIR.[F1] ="1 Foney Road") AS main 
         GROUP BY 
             main.[wF7], main.[F9], main.[F6] 
         HAVING 
             Sum(main.[F10]) = 0 
         ORDER BY 
             Max(main.[sF7]), main.[wF7];

I found that when using a group by, I needed to use a having clause with the same sum statement used within the select.

Upvotes: 0

Views: 2644

Answers (1)

Parfait
Parfait

Reputation: 107652

As mentioned, consider an aggregate query grouped by first three columns, F7, F9, F6, summing F10 and taking the max of all other non-grouped columns. Max/Min works on strings and numbers.

Notice your original query embedded as a derived table with aggregate on outside. Below is the SQL version to be incorporated into your VBA string. Do not use Join() due to GROUP BY:

SELECT main.[wF7], main.[F9], main.[F6], Max(main.[F8]) As maxF8, 
       Sum(main.[F10]) As sumF10, Max(main.[F11]) As maxF11, 
       Max(main.[F15]) As maxF15, Max(main.[F25]) As maxF25, Max(main.[sF7]) As maxsF7
FROM

  (SELECT sWIR.[F7] As wF7, sWIR.[F9], sWIR.[F6], sWIR.[F8], sWIR.[F10],
          sWIR.[F11], sWIR.[F15], sWIR.[F25], sCodes.[F7] As sF7
   FROM [" & SourceWIR & "] AS sWIR 
   INNER JOIN [" & SourceCODES & "] AS sCodes 
       ON sWIR.[F5] = sCodes.[F6]
   WHERE sWIR.[F1] ='" & Address & "') AS main

GROUP BY main.[wF7], main.[F9], main.[F6]
ORDER BY Max(main.[sF7]), main.[wF7]

Upvotes: 1

Related Questions