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 cannot set referential integrity on the join line.
Manipulating the Field List window
Each Field List window begins at a fixed size, which shows approximately four fields and perhaps 12 characters for each field. Each Field List is a resizable window and can be moved within the Query Designer. If there are more fields than will show in the Field List window, a scroll bar enables you to scroll through the fields in the Field List.
Note
After a relationship is created between tables, the join line remains between the two fields. As you move through a table selecting fields, the line moves relative to the linked fields. For example, if the scroll box moves down (toward the bottom of the window) in tblContacts, the join line moves up with the customer number, eventually stopping at the top of the table window.
When you’re working with many tables, these join lines can become confusing as they cross or overlap. As you scroll through the table, the line eventually becomes visible, and the field it is linked to becomes obvious.
Moving a table
Move the Field Lists by grabbing the title bar of a Field List (where the name of the table is) with the mouse and dragging the Field List to a new location. You may want to move the Field Lists for a better working view or to clean up a confusing query diagram.
You can move and resize the Field Lists anywhere in the top pane. Access saves the arrangement when you save and close the query. Generally speaking, the Field Lists will appear in the same configuration the next time you open the query.
Removing a table
There are times when you need to remove tables from a query. Any table can be removed from the Query window. Use the mouse to select the table you want to remove in the top pane of the Query window and press the Delete key. Or right-click on the Field List and choose Removed Table from the shortcut menu.
Caution
When you delete a table, join lines to that table are deleted as well. When you delete a table, there is no warning or confirmation dialog box. The table is simply removed from the screen, along with any of the table’s fields added to the QBE grid.
Adding more tables
You may decide to add more tables to a query or you may accidentally delete a table and need to add it back. You accomplish this task by clicking on the Show Table button from the Query Setup group in the Design tab. The Show Table dialog box appears in response to this action.