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.

Combine Table Items

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.
third table has all item combinations
third table has all item combinations

Use MS Query

I’ve done this type of item combining 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.

two tables in MS Query with no join
two tables in MS Query with no join

Read the Details

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

The instructions on that show all the steps for creating the MS query, and then sending the query results to Excel, and finally, refreshing the table if the source data changes.

_____________________

0 thoughts on “Create Table Combinations With MS Query”

  1. 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. 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!

  3. 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 AlexJ Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.