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 |
|---|
| Hat |
| Shirt |
| Gloves |
| Pants |
| Shoes |
and a color list
| 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 |
|---|
| 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;