Contextures

Excel Cartesian Join with Microsoft Query

How to create a Cartesian join (cross join) between two tables in Excel, using Microsoft Query. This connects all items in table 1 with all items in table 2, to list all possible combinations.

Set up the Workbook

In this example, there are two tables -- Raw Materials and Packaging -- and each table is on a separate worksheet.

The Raw Materials table is on the sheet named Materials, and the Packaging table is on the sheet named Packaging.

The third sheet in the workbook is named Combined, and this is where the query results will be stored.

cartesian join with ms query

With Microsoft Query, you can create a list that combines each item in one table, with all the items in the other table -- a Cartesian join, also called a cross join. You can read more about the different join types on the Microsoft website: Description of the usage of joins in Microsoft Query

Open MS Query

To create the Cartesian (cross) join, you'll use MS Query.

  1. On the Excel Ribbon, click the Data tab
  2. In the Get External Data group, click From Other Sources, then click From Microsoft Query

    Data from Microsoft Query

  3. In the Choose Data Source window, click on Excel Files*, and click OK

    choose data source

  4. In the Select Workbook window, locate and select the current workbook, and click OK.

    Select Workbook window

  5. In the Query Wizard, if you don't see the sheet names listed, click Options, and add a check mark to System Tables

    Query Wizard

  6. Click Materials$, and click the arrow to put the Raw Materials column in the query.
  7. Click Packaging$, and click the arrow to put the Packaging column in the query

    choose columns

  8. Click Cancel, to close the Query Wizard, and click Yes when prompted.

    close the query wizard

  9. In Microsoft Query, double-click on Raw Materials in the Materials$ table, to add it to the query grid.
  10. Then, double-click on Packaging, to add it to the query grid.

    add fields in MS Query

  11. Click the Return Data button, to send the data to Excel.

    send the data to Excel

Create a Worksheet Table

It might take a few seconds, but then the Import Data window will open.

  1. In the Import Data window, select Table
  2. Select the cell on the worksheet where you want to place the query results, and click OK.

    Import Data window

  3. A table is created, and shows all the items from each table, in all possible combinations.

    Query results table

Add Formulas to the Table

You can add formulas to the table, in a new column. The formulas will automatically adjust if the source tables are changed.

  1. Type a new heading in cell C1 -- MatPack -- and press Enter
  2. A new column is automatically included in the table.
  3. In cell C2, type a formula to combine the text in columns A and B, with a space character between them:

    =[@[Raw Materials]] & " " & [@Packaging]

  4. Then, copy the formula down to the last row of data in the table.

    add formula in table

Update the Table

You can update the source tables, and then update the query results table, to show the revised data.

  1. Add a new item to each of the source tables. In this example, Boxes was added to the Packaging list, and Cream was added to the Raw Materials list.

    update the source data

  2. On the Excel Ribbon, click the Data tab, and click Refresh All.

    refresh all

  3. The new items are shown in the updated query results table.

    updated query results table

Manually Update the Workbook Name

If you change the workbook name, the query will need to be updated, before it will run. To manually update the query:

  1. Right-click a cell in the results table, and click Refresh
  2. When the Login Failed message appears, click OK

    Select Workbook window

  3. In the Select Workbook window, locate and select the new workbook, and click OK.

Select Workbook window

Update the Query Connection with VBA

If the file name or location will change frequently, you can use programming to automatically change the file location in the connection.

Paste the following code -- FixQueryConnection -- into a regular module in the workbook, and then run the code when the workbook opens, by adding a Workbook_Open event.

This code was tested in Excel 2010 (32-bit), and might need to be adjusted for other versions of Excel.

Sub FixQueryConnection()
'
Dim strFile As String
Dim strPath As String
Dim strQry As String
Dim strCmd As String
Dim strConn As String

strPath = ActiveWorkbook.Path & "\"
strFile = ActiveWorkbook.Name
strQry = "Query from Excel Files"
strCmd = "SELECT `Materials$`.`Raw Materials`, `Packaging$`.Packaging "
strCmd = strCmd & "FROM `Materials$` `Materials$`, `Packaging$` `Packaging$`"
strConn = "ODBC;DSN=Excel Files;DBQ=" & strPath & strFile
strConn = strConn & ";DefaultDir=" & strPath
strConn = strConn & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
'
    With ActiveWorkbook.Connections(strQry).ODBCConnection
        .BackgroundQuery = True
        .CommandText = strCmd
        .CommandType = xlCmdSql
        .Connection = strConn
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections(strQry)
        .Name = strQry
        .Description = ""
    End With
    ActiveWorkbook.Connections(strQry).Refresh
    
    ActiveWorkbook.RefreshAll
End Sub
'====================================          

Put this code into the ThisWorkbook module:

Private Sub Workbook_Open()
  FixQueryConnection
End Sub
'====================================          

 

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 21, 2016 7:58 PM
Contextures RSS Feed