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


and a color list


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.



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
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

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

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

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