Matrix Table Query

[Arlmachado]

I am working with a matrix table like the one below:

And I want to basically gather all the combinations of the vertical and horizontal values, where the middle value is equal to 2.

Or basically whenever there is a middle value of two, which are the correspoding vertical values and horizontal values. Is there a query type where I can gather this information in table form?

 

[Minty]

That looks like badly stored data. If your field names are actually the objects you are storing then you do not have normalised data.

Have a read of the guide in my signature or any of the threads listed at the bottom of this page.

 

[sneuberg]

Where is this data currently stored, i.e., database table, spreadsheet, or something else?

 

[Arlmachado]

Thanks, but I am trying to get the best out of the tables I am given.

The middles values (1,2 and 3) are a code used to signify whether or not a combination is between the vertical field name and horizontal field name. The value 2 signifies it is possible to combine both names. I just wanted a way to quickly find all the correspoding vertical and horizontal fields to the value 2, and create a table from there. So that I don´t have to do it manually. Sorry should have explained better

 

[Arlmachado]

Where is this data currently stored, i.e., database table, spreadsheet, or something else?

It´s on Excel at the moment, but I will export it to Access since I will be working from there on.

 

[sneuberg]

I suggest creating your table directly from the Excel spreadsheet. You could use Excel Automation to scan through the matrix and build your table Below is some code I wrote to do something similar but that spreadsheet data was more complex than in your situation. If you give me the details of your spreadsheet or zip and upload it I'll tailor this code to your situation.

Private Sub MakeKJMixTable()
    
    
Dim strFile As String
strFile = SelectFile
'strFile = "C:\test.xlsx"
If Len(strFile & "") = 0 Then
    Exit Sub
End If
       
Dim my_xl_app As Object
Dim my_xl_worksheet As Object
Dim my_xl_workbook As Object
Set my_xl_app = CreateObject("Excel.Application")
Set my_xl_workbook = my_xl_app.Workbooks.Open(strFile)
Set my_xl_worksheet = my_xl_workbook.Worksheets("KJ Mix-Edit Here")
Dim ItemStartCol As Integer
Dim ItemEndCol As Integer
Dim ItemRow As Integer
Dim AltItemRow As Integer
Dim Item As String
Dim PROMO As String
Dim AltItem As String
Dim ItemQuantity As String
Dim PromoStartRow As Integer
Dim PromoEndtRow As Integer
Dim PromoCol As Integer
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Dim strSQL As String

Set db = CurrentDb
db.Execute "CREATE TABLE [KJMixTBL] ([PromoName] TEXT, [ItemName] TEXT, [ItemAltName] TEXT, [IQuantity] TEXT);"
PromoStartRow = 7
PromoEndtRow = 97
PromoCol = 1
ItemStartCol = 2
ItemEndCol = 65
ItemRow = 1
AltItemRow = 2

For i = ItemStartCol To ItemEndCol
    Item = my_xl_worksheet.Cells(ItemRow, i).Value
    AltItem = my_xl_worksheet.Cells(AltItemRow, i).Value
    Debug.Print "Col " & i & "  " & Item
    For j = PromoStartRow To PromoEndtRow
        PROMO = my_xl_worksheet.Cells(j, PromoCol)
        ItemQuantity = my_xl_worksheet.Cells(j, i)
         strSQL = "INSERT INTO [KJMixTBL] ([PromoName], [ItemName], [ItemAltName], [IQuantity]) VALUES ('" _
                & SQP(PROMO) & "','" & SQP(Item) & "','" & SQP(AltItem) & "','" & ItemQuantity & "');"
        db.Execute strSQL
   Next j
Next i

Set my_xl_worksheet = Nothing
my_xl_workbook.Close SaveChanges:=False
Set my_xl_workbook = Nothing
Set my_xl_app = Nothing
db.Close

End Sub

Public Function SelectFile() As String

Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
 
With fdg
    .AllowMultiSelect = False
    .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems 'onby be 1
            strSelectedFile = vrtSelectedItem
        Next vrtSelectedItem
        SelectFile = strSelectedFile
    End If
End With
Set fdg = Nothing

End Function

 

[Arlmachado]

Thank you so much, it would be so amazing if I could get a table will all those possible combinations without having to do it manually.

 

[sneuberg]

The database to convert the spreadsheet to a table is attached. This is tailored to the spreadsheet you uploaded so if the layout of the spreadsheet changes the code will need changing. If this is going to be more than a one time thing then some error checking should be added to the code. All of the cell contents were imported as text. If this is a problem let me know. The database also includes a query that retrieves the records with a cell value of '2'.

 

[Arlmachado]

Thank you so much. Just a quick question how do I run this database that you have made for the data with the excel table? If I need to change the middle values (1,2,3), how do I connect the excel table with the query?

 

[sneuberg]

If you are asking how you update the table with the spreadsheet you just open the frmImportMatrix form, select the file and click the Create Table button. The code deletes the existing table and creates a new one with the data from the selected spreadsheet.

If the layout of the spreadsheet changes you will need to change some of the variables in the code. If you can't figure it out let me know.

 

[Arlmachado]

Thanks! this works perfectly. I do have one question, so I can learn to change it a bit. How do I name the colummns? for example the H1_Row?

Again I am very thankful for the help

 

[sneuberg]

If you mean the table fields names they are determined by the names in the CREATE TABLE statement but if they are changed there the corresponding field in the INSERT statement must be changed also. For example in the code below I changed the field that was named [Produkt] to [Produkt-/Maßnahme-/Kombi- Bezeichnung]. The two places are shown in red below

Private Sub CreateTable_Click()

If Len(Me.FilePath & "") = 0 Then
    MsgBox "Please select an Excel File"
    Exit Sub
End If
       
If TableExists("tblMatrix") Then
    CurrentDb.Execute "Drop Table tblMatrix"
End If

       
Dim my_xl_app As Object
Dim my_xl_worksheet As Object
Dim my_xl_workbook As Object
Set my_xl_app = CreateObject("Excel.Application")
Set my_xl_workbook = my_xl_app.Workbooks.Open(Me.FilePath)
Set my_xl_worksheet = my_xl_workbook.Worksheets("Matrix")
Dim MatrixStartColumn As Long
Dim MatrixEndtColumn As Long
Dim H1Row As Long
Dim H2Row As Long
Dim strH1 As String
Dim strH2 As String
Dim strNA_AA As String
Dim strProdukt As String
Dim CellValue As Variant
Dim MatrixStartRow As Long
Dim MatrixEndRow As Long
Dim NA_AAColumn As Long
Dim ProduktColumn As Long
Dim i As Long
Dim j As Long
Dim strSQL As String

DoCmd.Hourglass True
CurrentDb.Execute "CREATE TABLE [tblMatrix] ([NA_AA] TEXT, [COLOR="Red"][Produkt-/Maßnahme-/Kombi- Bezeichnung][/COLOR] TEXT, [H1] TEXT, [H2] TEXT, [Cell] TEXT );"
MatrixStartRow = 3
MatrixEndRow = 49
NA_AAColumn = 1
ProduktColumn = 2
MatrixStartColumn = 3
MatrixEndtColumn = 48
H1Row = 1
H2Row = 2

For i = MatrixStartColumn To MatrixEndtColumn
    strH1 = my_xl_worksheet.Cells(H1Row, i).Value
    strH2 = my_xl_worksheet.Cells(H2Row, i).Value
   ' Debug.Print "Col " & i & "  " & strH1
    For j = MatrixStartRow To MatrixEndRow
        strNA_AA = my_xl_worksheet.Cells(j, NA_AAColumn)
        strProdukt = my_xl_worksheet.Cells(j, ProduktColumn)
        CellValue = my_xl_worksheet.Cells(j, i)
        strSQL = "INSERT INTO [tblMatrix] ([NA_AA], [COLOR="red"][Produkt-/Maßnahme-/Kombi- Bezeichnung][/COLOR], [H1], [H2], [Cell]) VALUES ('" _
                & SQP(strNA_AA) & "','" & SQP(strProdukt) & "','" & SQP(strH1) & "','" & SQP(strH2) & "','" & CellValue & "');"
        CurrentDb.Execute strSQL
   Next j
Next i

Set my_xl_worksheet = Nothing
my_xl_workbook.Close SaveChanges:=False
Set my_xl_workbook = Nothing
Set my_xl_app = Nothing
DoCmd.SelectObject acTable, , True
DoCmd.Hourglass False

End Sub

 

[Arlmachado]

Great! Thanks, you have been an amazing help. Cheers