Chapter 4: Selecting Data with Queries
159
If referential integrity is set on the relationship, Access uses a somewhat thicker line for the join
connecting to the table in the query designer. A one-to-many relationship is indicated by an infin-
ity symbol (∞) on the many-side table end of the join line.Access auto-joins two tables if the following conditions are met:l (^) Both tables have fields with the same name.
l The same-named fields are the same data type (text, numeric, and so on).
l (^) One of the fields is a primary key in its table.
Manipulating Field Lists
Each Field List begins at a fixed size, which shows a number of fields and several leading charac-
ters of each field name. Each Field List window is resizable 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.
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 you scroll down-
ward, towards the bottom of the window in tblCustomers, the join line moves upward 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 window (where the name of the table
is) with the mouse and dragging the Field List window 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
You might need to remove tables from a query. 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 window and choose Remove Table from the shortcut menu.
Removing a table from a query’s design does not remove the table from the database, of course.