Microsoft Access produce Cartesian product with Cross Join

This Microsoft Access tutorial explains how to produce Cartesian product with Cross Join in Access Query.

You may also want to read:

Create Cartesian product with Excel Query

Microsoft Access Cartesian product with Cross Join

In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join.

Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most of the time we hate Cartesian product because it is usually a result of duplicated keys. Sometimes we need it because it helps us join unrelated tables together to form thousands of possible combination very quickly.

Example – create Access Cartesian product with Cross Join

To Illustrate, let’s say we have a product list

product_tbl
Product
Hat
Shirt
Gloves
Pants
Shoes

and a color list

color_tbl
Color
Red
Orange
Yellow
Green
Blue
Purple

Our goal is to join the two Tables so that each Product has all 6 different colors.

Create a new Query. In Query Design View, add the two tables, select all the fields from the two Tables.

 

Run the Query, now each product has 5 colors.

 

We can Cross Join more than 2 Tables. Suppose each color is further divided into Light color and Dark color, as defined in color2_tbl.

color2_tbl
Color2
Light
Dark

 

Open the Query Design View, add the color2_tbl

 

Run the Query

Similar to normal Query, you can also type criteria in Cross Join.

Cross Join in SQL View

Unlike other SQL applications, Microsoft Access does not allow you to type the keyword Cross Join in the SQL, you only need to type all the table names you want to Cross Join in the FROM statement.

SELECT product_tbl.Product, color_tbl.Color, color2_tbl.Color2
FROM product_tbl, color_tbl, color2_tbl;

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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