Coffee break - Access transform-pivot scavenging.

Looking for articles on Transform - Pivot, ie. crosstab queries in Access for my VBA ACE SQL solutions. I need it because my Excel files need to work with 100k+ data, and a lot of filtering, joining etc. Interesting: http://blogannath.blogspot.com/2010/02/microsoft-access-tips-tricks-crosstab.html#ixzz3t3Pb0e6j And more. http://datapigtechnologies.com/blog/index.php/running-crosstab-queries-in-excel/ http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/ http://allenbrowne.com/ser-67.html http://www.mrexcel.com/forum/microsoft-access/261348-help-transform-pivot-crosstab-querys-access.html http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/

Fuzzy matching columns revisited.

Have you ever had to join two tables with spelling and typing errors, variants of the same name etc.? Try an UDF (user defined function in Excel) using NGrams and DiceCoefficient... my recommendation:
http://www.niftytools.de/2015/03/fuzzy-string-matching-excel/

Another option is to use an excelent vba macro which uses Levenstein and several other routines for fuzzy matching. http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

Moreover, it is often the case that we want to join tables on strings that exist within names in a column of another table. Then you may use the following formula (array formula, press Ctrl+Shift+Enter):

{=INDEX(kolumna_wartosci;MATCH(FALSE;ISERROR(SEARCH(kolumna_przeszukiwana;szukana_wartosc));0))}

Other options... search google for fuzzy lookup udf: np. stackoverflow: http://stackoverflow.com/questions/13291313/matching-similar-but-not-exact-text-strings-in-excel-vba-projects
--------------
In MS SQL you can try using SimMetrics library and compare various routines:   http://anastasiosyal.com/POST/2009/01/11/18.ASPX

And if we look for strings (in a column in TableB) in a column with long names  in TableA, we can try:

SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b
   FROM TableB
   RIGHT JOIN TableA
   ON  TableA.Descr LIKE ("*"+TableB.Descr+"*")
   GROUP BY  TableA.descr,b;

MSAccess i VBA - kursy

Ostatnio potrzebuję się poduczyć: w sieci dużo darmowych i wartościowych treści, np:  http://vbahowto.com/ - zaciekawił mnie temat Recordset'ów (moduł 9), programowania formularzy, cdn.
===
Implementing Data Warehouse...
http://www.siop.org/tip/backissues/April%2005/18weiss.aspx
http://sqlblogcasts.com/blogs/drjohn/archive/2010/02/14/microsoft-access-an-elegant-solution-to-data-warehouse-metadata.aspx

Business Intelligence
http://www.slideshare.net/DhatriJain/data-mining-with-ms-access
http://www.databasejournal.com/features/msaccess/article.php/3871841/Microsoft-Access-Business-Intelligence-on-a-Shoestring.htm
http://www.fmsinc.com/microsoftaccess/dataanalysis/versus-excel.html

http://www.amazon.com/Data-Analysis-Microsoft-Access-2010/dp/1435460103

Podstawy accessa:
http://www.functionx.com/access/

SQL w Excelu Cz.4: Odpowiednik MS SQL ROLLUP'a ciąg dalszy.

W poprzednim poście sumowałem grupy produktów, w których zgadzały się pierwsze trzy litery kodu. Teraz inne zadanie - mam pogrupować grupy produktów na podstawie wyrażeń występujących w ich nazwach. Przy czym w osobnej grupie ma się znaleźć produkt xyz a w osobnej xyz pro. Nie mogą zsumować się w jednej grupie produkty xyz i xyz pro!. Poniżej kod i pliki przykładowe.

Przetestowane w MS Access query (kluczowe jest tutaj użycie
- w SELECT:  max(TableB.Descr)
- połączenia ON  TableA.Descr LIKE (TableB.Descr+"*")

SELECT opis, wartosc
FROM (SELECT DISTINCT "0" as col_order,  opisszczegolowy as opis, ""  as wartosc, majorID FROM
  (SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID, first(TableB.Details) as opisszczegolowy
   FROM TableB 
   RIGHT JOIN TableA 
   ON  TableA.Descr LIKE (TableB.Descr+"*") 
   GROUP BY  TableA.descr,b )

UNION ALL

SELECT  "1" as col_order,  opis, b as wartosc, majorID FROM
  (SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID
   FROM TableB 
   RIGHT JOIN TableA 
   ON  TableA.Descr LIKE (TableB.Descr+"*") 
   GROUP BY  TableA.descr,b )

UNION ALL

SELECT   "2" as col_order, major+" Total" as opis, sum(b) as wartosc, first(majorID) FROM
  (SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID
   FROM TableB 
   RIGHT JOIN TableA 
   ON  TableA.Descr LIKE (TableB.Descr+"*") 
   GROUP BY  TableA.descr,b )
GROUP BY major

UNION ALL

SELECT   "3" as col_order, "Grand Total" as opis, sum(b) as wartosc, "999999" as majorID FROM TableA

)  AS [Rollup_Result]
ORDER BY majorID, col_order;

A teraz trzeba rozwiązanie przekształcić na string SQL ACE OLEDB. W pierwszym kroku użyłem do tego celu narzędzia SQLinFormpro do formatowania łańcuchów SQL w VB (do 100 linii za darmo). Bardzo pomocne narzędzie, dzięki sensownym wcięciom pomogło mi odnaleźć się w gąszczu kodu:

Public Sub SporzadzOferte()
Dim Myconnection As Connection
Dim Myrecordset As Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer

'Deal with input data range so that it refers to a Table and not to a Sheet!
'It will allow writing notes and puting a header above the table


Dim rngTableA As String

    rngTableA = Replace(Sheets("TableA").Range("TableA[#All]").Address, "$", "")
    rngTableA = "[TableA$" & rngTableA & "]"
 
'Debug.Print rngTableA


'--------------------

Set Myconnection = New Connection
Set Myrecordset = New Recordset
  
 
  
'Identify the workbook you are referencing
    Myworkbook = Application.ThisWorkbook.FullName

'Open connection to the workbook
   Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & Myworkbook & ";" & _
                      "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"

 
strSQL = "" & _
"SELECT   opis, wartosc " & _
"FROM     (SELECT DISTINCT '0'               AS col_order," & _
"                          [opisszczegolowy] AS [opis],       " & _
"                          ''                AS [wartosc]," & _
"                          majorID  " & _
"         FROM    (SELECT    " & rngTableA & ".descr      AS opis  ,  " & _
"                            MAX([TableB$].Descr)         AS [major] ,  " & _
"                            b                          ,  " & _
"                            first([TableB$].ID)          AS [majorID],  " & _
"                            first([TableB$].[Details])   AS opisszczegolowy " & _
"                 FROM       [TableB$] " & _
"                            RIGHT JOIN " & rngTableA & " " & _
"                            ON         " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
"                 GROUP BY   " & rngTableA & ".descr, b) " & _
"    UNION ALL "

strSQL = strSQL + "           " & _
"          SELECT '1' AS col_order,  " & _
"                 opis          ,  " & _
"                 b AS wartosc  ,  " & _
"                 majorID  " & _
"          FROM   (SELECT    " & rngTableA & ".descr      AS opis ,  " & _
"                            MAX([TableB$].Descr)         AS major,  " & _
"                            b                         ,  " & _
"                            first([TableB$].ID)          AS majorID " & _
"                 FROM       [TableB$] " & _
"                            RIGHT JOIN " & rngTableA & " " & _
"                            ON         " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
"                 GROUP BY   " & rngTableA & ".descr,  b) " & _
"    UNION ALL "

strSQL = strSQL + "           " & _
"          SELECT   '2'            AS col_order,  " & _
"                   major+' Total' AS opis     ,  " & _
"                   SUM(b)         AS wartosc  ,  " & _
"                   first(majorID)  " & _
"          FROM     (SELECT    " & rngTableA & ".descr      AS opis ,  " & _
"                              MAX([TableB$].Descr)         AS major,  " & _
"                              b                         ,  " & _
"                              first([TableB$].ID)          AS majorID " & _
"                   FROM       [TableB$] " & _
"                              RIGHT JOIN " & rngTableA & " " & _
"                              ON         " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
"                   GROUP BY   " & rngTableA & ".descr, b  ) " & _
"          GROUP BY major " & _
"    UNION ALL "

strSQL = strSQL + "           " & _
"          SELECT '3'            AS col_order,  " & _
"                 'Grand Total'  AS opis     ,  " & _
"                 SUM(b)         AS wartosc  ,  " & _
"                 '999'          AS majorID  " & _
"          FROM   " & rngTableA & " " & _
"         )  " & _
"ORDER BY [majorID], [col_order]"

'Load the Query into a Recordset
    Myrecordset.Open strSQL, Myconnection, adOpenStatic

    
'Place the Recordset onto SheetX
   With Sheets("Oferta")
    .Activate
    .ListObjects("Table3").DataBodyRange.Rows.Delete
    .Range("A5").CopyFromRecordset Myrecordset
    
 
    'Add column heading names
    For i = 1 To Myrecordset.Fields.Count
    .Cells(4, i).Value = Myrecordset.Fields(i - 1).Name
    Next i
   
   End With
   
   
   
   Set Myrecordset = Nothing
   Myconnection.Close
   
Call BoldAndInsertEmptyRow
   
        
End Sub 

Uwaga, podczas edycji otrzymałem komunikat, że string SQL był za długi, więc musiałem go podzielić na kilka stringów. Dzięki temu łańcuch SQL stał się jeszcze bardziej czytelny, pojawiło się miejsce na komentarze między łączonymi tabelami.
Pozostało formatowanie. Uwaga, odkryłem, że funkcja val nie jest dobra do konwersji liczb w postaci tekstowej do liczb w formacie currency, powoduje utratę miejsc po przecinku, w jej miejsce zastosowałem CDbl

Option Explicit


Sub BoldAndInsertEmptyRow()

    Dim oListObj As ListObject
    Dim RowCnt As Long
    Dim r As Long

    Set oListObj = Worksheets("Oferta").ListObjects("Table3") 'change the sheet and table names accordingly
    
    Application.ScreenUpdating = False
   
' Solve the problem with values not showing up in proper (currency) format but in text format.
    Dim Cell As Range
    
    For Each Cell In oListObj.ListColumns("wartosc").DataBodyRange
    
           With Cell
               If .Value <> "" Then
                   .NumberFormat = "#,##0.00 $"
                   .Value = CDbl(.Value)  'not CCur(Val(.Value)) because you are loosing fractions.
               End If
           End With
    
    Next Cell

   
    '==========
'Format rows with totals: add empty row above, frame.
    
    RowCnt = oListObj.ListRows.Count
    
    For r = RowCnt To 1 Step -1
        With oListObj.ListRows(r).Range
            
            'If contains "TOTAL" - bold ent.row and add empty row below.
            
            If InStr(UCase((.Cells(1, 1).Value)), "TOTAL") > 0 Then
                .Font.Bold = True
                
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThick
                End With

                If r < RowCnt Then
                    oListObj.ListRows.Add Position:=r + 1, alwaysinsert:=True
                End If
            End If
            
             ' format rows with description
            
            If .Cells(1, 2).Value = "" Then

                .Cells(1, 1).Font.Bold = True
                
                
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
            
            End If
            
          
        End With
    Next r


Application.ScreenUpdating = True

End Sub

Pliki przykładowe: test zapytania SQL w MSAccesie działająca formatka z zapytaniem ACE SQL w Excelu.

Lookup: Przypisz krótkie nazwy długim używając słownika.

W danych, które otrzymałem, występują nazwy firm w różnych wariantach. Mogę być pewien, że przynajmniej jeden człon, może jeden wyraz, będzie identyfikował poszczególne firmy. Tworzę więc słowniczek takich skróconych nazw, które występują w mojej bazie. Jak je przypisać?

1. Przetestowałem w Accesie:
Table_A to moja baza danych
Table_B to mój słowniczek skróconych nazw, które występują w długich nazwach.

SELECT Table_A.Full_name, Table_B.Short_name, Table_B.ID_firmy
FROM Table_B RIGHT JOIN Table_A ON  Table_A.Full_name Like "*"+Table_B.Short_name+"*";

2. Przetestowałem również w Excelu różne rozwiązania i ostatecznie otrzymałem podpowiedź na forum Altkom Pana Krzysztofa Rutkowskiego:

 

Słowniczek
Excomers	1
Futuro	2
Polexpo	3
Baza zewnętrzna	Tutaj działa formuła
Przedsiębiorstwo Handlu Polexpo.	3
Dystrybutor - FUTURO PUH	2
Excomers przedsiębiorstwo handlu	1
Przeds. Futuro Sp. z o.o.	2
Jan Kowalski Excomers.	1
polexpo	3
excomers	1

={INDEX($D$6:$D$8;MATCH(FALSE;ISERROR(SEARCH($C$6:$C$8;C12));0))}
Uwaga, formuła tablicowa Ctrl+Shift+Enter
Uwaga, słowniczek należy posortować malejąco.

gdzie C12 to nazwa długa w Bazie
zaś w słowniczku szukamy: $C$6:$C$8 to nazwa krótka, w zakresie $D$6:$D$8 - ID firmy,. Jeśli zaś do przyporządkowania nie ma zbyt wielu krótszych nazw, w Accesie można użyć switch'a a w MS SQLu IN.

SELECT 
 switch(
   Table1.Nazwa LIKE  "*Vicryl PLUS*", "Vicryl PLUS",
   Table1.Nazwa LIKE  "*Vicryl*" And  Table1.Nazwa Not LIKE "*PLUS*" , "Vicryl",
   Table1.Nazwa LIKE  "*Monocryl PLUS*", "Monocryl PLUS",
   true,"N/A"
   
 ),

Table1.Nazwa, Table1.Wartość
FROM Table1