Create Table Combinations With MS Query

What’s a quick way to combine the items in two table? For example:

  • Table A has 3 items – Sugar, Coffee and Milk.
  • Table B has 2 items – Cans and Sticks.

How can you create a third table that has all the Table 1 items combined with each of the Table 2 items?

  • Sugar – Cans, Sugar – Sticks, etc.

cartesianjoin00

Use MS Query

I’ve done this with programming before, but this time I used Microsoft Query, to do the work for me.

Add the two tables to the query, with no join line between them, and the results show each item in table 1 connected to each item in table 2.

cartesianjoin07

Read the Details

To see the details for setting this up, and refreshing the results table, please visit the Cartesian Join in Excel Using MS Query page on my Contextures website.

It shows all the steps for creating the query, sending the results to Excel, and refreshing the table if the source data changes.

_____________________

You may also like...

5 Responses

  1. AlexJ says:

    Debra,
    I’ve been doing some of this lately. One of the issues is that saving the file under a different name requires the connection string to be updated. Any utilities for that?

  2. @AlexJ, I added sample code on the Contextures page, to automatically update the connection string when the file is opened. Does that help?

    http://www.contextures.com/excelmsquerycartesian.html

  3. christian.a says:

    Debra,

    I have a worksheet connected to an MS query that joins 2 excel tables. Lately, upon refresh, some columns values are being return as text values rather than numbers. This creates an issue for SUM formulas.

    The formats for excel table feeding the query have not changed, and i also tried formatting the data to currency but the output of the query is still in text format. I validate all the numerical output using TYPE function and some return the value 2. Could you help to explain why this is happening?

    Thanks in advance!

  4. AlexJ says:

    Christian – the data type returned through the query follows SQL typing rules – it reacts badly to mixed data types, as I believe you know. The query returns the type for a field based on the initial entry (or entries) in the source field. In your case, I would guess that any values in the source numeric field should be set explicitly to zero (not space or blank).

    Alternately, you could add a column to your query result range or table which multiplies by one -forcing a numeric value. You can make the formula in the new column update with the query.

Leave a Reply to christian.a Cancel reply

Your email address will not be published. Required fields are marked *