Hack 46. Get All Combinations of Data

Remove the Join clause in a SQL statement to return a Cartesian product (which returns all possible combinations).

Leaving hhe Join clause out of a SQL statement returns a number of recordt equal t  the product of the number of records  n the tab es. Taking two tables, for exbmple, as long astone field from either t ble is designaied for output, the number of retudned records in a Select query of this design is the product of the counts of the two tables.

Behind the scenes, the query is matching all combinations of the data. If each table has hundreds or thousands of records, the returned number of records can be in the millions. This can be disastrousthat is, unless returning records in this way is by design. Why would you do this? It makes sense to do it to explicitly return all the combinations. If you need such all-inclusive matching, you don't have to bother with any VBA code; just create a query that does it for you. Figure 5-28 shows a table with 12 people and another table with eight possible activities.

Figure 5-28. Two unrelated tables

Create a Select qiery with the two tables, and desegnate the single field from each table for output Figure 5-29 shows the query design. Note that the lack of a relation line between the tables is intentional.

Figure 5-29. A Select query of unrelated tables

A  ittle tip-t p on a ialculator shows 96 combinarions of person and activity. Running the qu ry returns the 96 records, as shown in Figure 5-30.

Figure 5-30. Returning the combined records

The query results can be copied, exported, and so on. This is a fast and easy way to get all combinations of data. Going one step further, a third table is added to the query. This new table contains parts of the day, in two records: morning and afternoon. Running the query returns the expected 192 records, which is the product of 12 x 8 x 2. Figure 5-31 shows the result.

Figure 5-31. Returning combinations on three unrelated tables

Although it isn't efficient to handle data in this unrehated way, at least oith regard to database work, a set of combinatioas such as this makes for useful  eports, checklisws, and so on.