3
\$\begingroup\$

Using Excel, I have one sheet ("db") with data extracted from MES. I have to analyze this data and I need: Articolo, Fase, Tipo Confezione, TURNO, Macchina, avg and median.

I searched everywhere how to get the median with SQL, but I found only how to get median for one item alone. Then I wrote this code, and I loop each item from 1st query to get data from another query, it works but it is extremely slow!

Is there a better way to get my data or, if my code is the correct way, how I can speed up the job? Any suggestions are appreciated.

My code:

Sub Get_Pivot()
    Dim strsql As String
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim RangeVal As Range, DatiVal
    
    Sheets("db").Select 'sheet with database
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
   ' select field from db
    strsql = "SELECT Articolo, Fase, [Tipo Confezione], TURNO, Macchina, avg(KgOra) as med FROM [db$] where Time>0 group by Articolo, Fase, [Tipo Confezione], TURNO, Macchina ;"
    rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
    
    rs.MoveFirst
    Sheets("pvt").Select  'sheet how to put data
    
    'insert header columns name
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
    Next i
    
    'insert data from recordest
    Range("A2").CopyFromRecordset rs
      
    'set range and get data from pasted recordset
    Set RangeVal = Range("A1", Cells(Range("A1").End(xlDown).Row, 7))
    DatiVal = RangeVal.Value
    Set rs = Nothing
    
'loop each row and run one qry to get median
For i = 2 To UBound(DatiVal, 1)
    strsql = "select last(KgOra) FROM (select top 50 percent KgOra from [db$] where " & _
            "Articolo = '" & DatiVal(i, 1) & _
            "' and Fase = '" & DatiVal(i, 2) & _
            "' and [Tipo Confezione] = '" & DatiVal(i, 3) & _
            "' and TURNO = '" & DatiVal(i, 4) & _
            "' and Macchina = '" & DatiVal(i, 5) & "');"
           
    rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
    
'if recordset exist add value at 7th filed of DatiVal
    If Not rs.EOF Then DatiVal(i, 7) = rs(0)
    rs.MoveNext
    Set rs = Nothing
Next

'then paste new data with one columns more(median columns)
whit RangeVal
.vaue = DatiVal
End With

rstErr:
    Set rs = Nothing
    Set cn = Nothing

End Sub
\$\endgroup\$
  • 2
    \$\begingroup\$ Note that this is exactly what pivot tables are supposed to do, although that's getting off-topic for this site. I.e. I think that if you asked on an Excel-specific site, I think that their advice would be to stop using VBA/SQL altogether and just use the built-in pivot table functionality. Example search result: masterdataanalysis.com/ms-excel/… \$\endgroup\$ – mdfst13 Nov 4 at 14:02
1
\$\begingroup\$

The key issue can be that you run the query with the same parameters multiple times. I assume you have quite many records where Articolo/fase/tipo/etc are the same for you are looking for median. You did not state how many records you have but I think you could reduce the number of issued queries. So - without knowing too much details - I would

  1. Collect unique Articolo/fase/tipo/etc values

  2. Issue the queries only once for each combination

  3. Update median values

| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

I don't use VBA in Excel, so I can't make specific code suggestions. But given this problem, I would be looking to create a new sheet.

On the new sheet, group your data by "Articolo, Fase, Tipo Confezione, TURNO, Macchina". And store the value that you are using to generate the average and median (it looks to be kgOra).

Then just generate the average and median for each group of data.

The problem that you are hitting is that you are scanning the entire table repeatedly to extract values. Stop doing that. Instead, sort the values once (into a new sheet). Then do your work on ranges. Once you've generated the new sheet, I would skip the SQL altogether. It should be easier to work performantly with just the ranges.

Note: if this weren't Excel, I would be suggesting a temporary table with an index. But as I said, in Excel, I think that it will be more efficient to work with the cells directly. An alternative solution would be to import the data into SQL Server or Access and create your temporary table there.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.