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 the Join clause out of a SQL statement returns a number of records equal to the product of the number of records in the tables. Taking two tables, for example, as long as one field from either table is designated for output, the number of returned 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 query with the two tables, and designate 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 little tip-tap on a calculator shows 96 combinations of person and activity. Running the query 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 unrelated way, at least with regard to database work, a set of combinations such as this makes for useful reports, checklists, and so on.