Access Hacks

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 disastrous—that 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 ...