Chapter 4: Selecting Data with Queries
161
If relationships are set in the relationship builder, you might not see the auto-join line if:l The two tables have a common field, but it isn’t the same name.l (^) A table isn’t related and can’t be logically related to the other table (for example, tblCus-
tomers can’t directly join the tblSalesLineItems table).
If you have two tables that aren’t related and you need to join them in a query, use the query
design window. Joining tables in the query design window does not create a permanent relation-
ship between the tables; instead, the join (relationship) applies only to the tables while the query
operates.
Tables in a query have to be joined in some way. Including two tables with nothing in common
(for example, a query based on tblCustomers and tblProducts) means that Access has no
way to know which records in the tblCustomers table match which records in the tblProd-
ucts table. Unless there is some way to relate the tables to one another, the query returns unus-
able data.
Caution
As a general rule, all tables in a query should be joined to at least one other table. If, for example, two tables in a
query aren’t joined in some way, the query produces a Cartesian product (also known as the cross-product) of the
two tables. (This subject is discussed in the “Creating a Cartesian product” section, later in this chapter.) For now,
note that a Cartesian product means that if you have five records in Table 1 and six records in Table 2, the result-
ing query will have 30 records (5 × 6) that will probably be useless.
Using ad hoc table joins
Figure 4.25 shows a simple query containing tblSales, tblSalesLineItems, tblProd-
ucts, and tblCategories. Notice that the join line between tblProducts and tblCatego-
ries is thinner than the other join lines, and does not include the 1 and infinity (∞) symbols. This
is an ad hoc join, formed when the Categories table was added to the query.
No formal relationship yet exists between tblProducts and tblCategories. However, Access
found the Category field in both the tables, determined that the Category data type is the same
in both tables, and that the Category field in tblCategories is the primary key. Therefore,
Access added an ad hoc join between the tables.
Note
Tables are not joined automatically in a query if they aren’t already joined at the table level, if they don’t have
a common named field for a primary key, or if the AutoJoin option is off.
If Access had not auto-joined tblProducts and tblCategories (perhaps because the
Category field was named differently in the tables), you can easily add an ad hoc join by drag-
ging the Category field from one table and dropping it on the corresponding field in the other
table.