• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

5. Queries and SQL > 46. Get All Combinations of Data

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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint