In the example shown in Figure 12-2, the Customers and the Orders tables have been
selected.
Notice that Access automatically makes a join between the ID field in the Customers table
and the Customer ID field in the Orders table. This is correct in joining the two tables.
Access has been doing a bit of thinking for us, which can be annoying sometimes, and has
surmised that there is a one-to-many relationship between customers and orders. In other
words, one customer can have many orders, and this is illustrated graphically by the 1 and 00
symbols on the join line.
If you want to join using other fields, you can right-click the join line and select Delete.
You can then select a field for joining in one table and drag it to the field you wish to join to
in the other table. This will create a new join line on the GUI.
You can have more than one join connecting two tables and have many different tables
within one query, although this does affect the query performance. The result is a “spider’s
web” that can be quite difficult to debug should there be anything wrong with it.
Because of this, Access has decided that this should be a right join so all orders are shown
regardless of whether there is a corresponding customer. This is illustrated graphically by the
small arrow on the left end of the join line.
Chapter 12: SQL Queries 151
Figure 12-2 The Query Design window showing the Customers and Orders tables