Part I: Access Building Blocks
160
Caution
When you remove a table from a query design, join lines to that table are deleted as well. There is no warning
or confirmation before removal. The table is simply removed from the screen, along with any of the table’s
fields added to the QBE grid. Be aware, however, that deleted tables referenced in calculated fields (calculated
fields are discussed in detail in Chapter 18) will not be removed. The “phantom” table references may cause
errors when you try to run the query.
Adding more tables
You might decide to add more tables to a query or you might accidentally delete a table and need
to add it back. You accomplish this task by clicking on the Show Table button on the Query Setup
group in the Design ribbon. The Show Table dialog box appears in response to this action.Creating and Working with Query Joins
By default, an Access query returns only records where data exists on both sides of a relationship.
This means, for instance, that a query that extracts data from the Contacts table and the Sales table
only returns records where contacts have actually placed sales, and will not show contacts who
haven’t yet placed a sale. If a contact record is not matched by at least one sales record, the contact
data is not returned by the query. This means that, sometimes, the query might not return all the
records you expect.The situation described in the preceding paragraph is called an inner join or an equi-join. Although
this is the most common join type between tables in a query, users sometimes want to see all the
data in a table (like the tblCustomers table in the preceding example), regardless of whether
those records are matched in another table. In fact, users often want to specifically see records that
are not matched on the other side of the join. Consider a sales department that wants to know all
the contacts who have not made a sale in the last year. You must modify the default query join
characteristics in order to process this type of query.You can create joins between tables in these three ways:l By creating relationships between the tables when you design the database.l (^) By selecting two tables for the query that have a field in common that has the same name
and data in both tables. The field is a primary key field in one of the tables.
l (^) By modifying the default join behavior.
The first two methods occur automatically in the query design window. Relationships between
tables are displayed in the query designer when you add the related tables to a query. It also cre-
ates an automatic join between two tables that have a common field, as long as that field is a pri-
mary key in one of the tables and the Enable Auto Join choice is selected (by default) in the
Options dialog box.