Office 2007 Bible

Working with the Table/Query Pane

As Figure 36-20 shows, a join line connects tables in the Query Designer. The join line connects the primary key in one table to the foreign key in another table.

Cross-Ref

These lines were predrawn because you already set the relationships between the tables earlier in Chapter 34.

The join line

A join line represents the relationship between two tables in the Access database. In this example, a join line goes from tblSales to tblContacts, connecting ContactID in the tblContacts table to the Buyer field in tblSales. There are other join lines connecting the other tables in this query.

The join line is automatically created because relationships were set in the relationship builder. If Access already knows about the relationship, it adds the join line when the tables are added to a query.

If Referential Integrity is set on the relationship, Access displays a thicker line where the join line connects to the table in the Query Designer. This variation in line thickness tells you that Referential Integrity is set between the two tables. If a one-to-many relationship exists, the many-side table is indicated by an infinity symbol (∞).

Access will auto join to tables if the following conditions are met:

• Both tables have fields with the same name.

• The same-named fields are the same data type (text, numeric, and so on).

• One of the fields is a primary key in its table.

Tip

Access 2007 automatically attempts to join the tables if a relationship exists. Access ...