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 ...
Get Access Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.