Microsoft Access 2010 Bible

Chapter 4: Selecting Data with Queries

169

True self-joins are relatively rare in most databases. But, it’s good to know that Access handles self-

joined tables with no trouble. Keep in mind that you must use an outer join in the self-joined query

design when it is important to see all of the records from one side or the other of the self-join.

Creating a Cartesian product

Adding tblCustomers and tblSales to a query without specifying a join between the tables

causes Access to combine every tblCustomers record with every tblSales record. Combining all

records in one table with all record in another table results in a Cartesian product (cross-product) of

the tables. Cartesian products contain thousands of records for even small tables and are not useful to

an application’s users.

Figure 4.32 shows a query design that results in a Cartesian product. In this particular example,

Access adds a join line between tblCustomers and tblSales because a formal relationship

exists between these tables in the Collectible Mini Cars database. For the purpose of this demon-

stration, I manually deleted the join line before adding several fields to the QBE grid.

FIGURE 4.32

A query that returns a Cartesian product set of records

Figure 4.33 shows a portion of the recordset returned by qryCartesianProduct. Notice that

every customer is included in the customer column, while the InvoiceNumber, SaleDate, and

InvoiceDate are repeated for every customer. In all, 1485 rows are returned, in spite of the rela-

tively small set of data in these tables.