Controlling Record Selection with Joins

With multiple tables in your query, you can control which records your query selects by altering or adding joins. Using the Join Condition dialog box, you can change the join type set between the tables.

Joins appear automatically when you add the tables. However, if the field names of related fields do not match, you might have to create your own joins between tables. You can create additional joins by dragging between the fields of tables in the Query Designer, or by choosing the Add Join button in the Query Designer toolbar to display the Join Condition dialog box.

When you add or alter a join, you can select a type of join to expand or narrow your results. The easiest way to create a join is by using the Join Condition dialog box.

To create a join between tables

1. Add two or more tables to a query.

2. In the Query Designer toolbar, choose Add Join.

3. In the Join Condition dialog box, select the related field names in the two tables.

   Note   Join columns only if they have the same size and data type.

4. Select a join type:

   To retrieve	Use
   Only records from both tables that match the join criteria, the most common type of join.	Inner Join
   All records from the table on the left side of the join criteria and only records that match the join criteria from the table on the right side of the join criteria.	Left Join
   All records from the table on the right side of the join criteria and only records that match the join criteria from the table on the left side of the join criteria.	Right Join
   All records from both tables whether or not they match the join criteria.	Full Join

5. Choose OK.

You can also delete or modify existing joins. Although you can still run a query without the joins, the results usually have little practical value and may require large amounts of time to process.

To delete a join

• Select the join line in the Query Designer and choose Remove Join Condition from the Query menu.

  -or-

  In the Join tab, select the join condition and choose Remove.

Besides filters and join types, you can control your results by changing the criteria used in the join. Joins do not have to be based on an exact match of fields; you can set up different join relationships based on Like, Exactly Like, More Than, or Less Than criteria.

Join criteria are similar to filter criteria; they both compare values and then include records that match the criteria. Unlike a filter, which compares a field value to a filter value, the join criteria compare the field value from one table to the field value of the other table.

For example, when you query two tables joined on their respective customer ID fields (Customer.cust_id = Orders.cust_id), the query retrieves only the records in which these two fields match, and which also meet any other filter you set in the query.

To modify a join

1. In the Join tab, select the join you want to modify.
2. Change the join conditions as needed.

See Also

Customizing Queries | Creating Queries | Creating a Query | Running Queries | Querying Multiple Tables and Views | Logical Operators | Join Condition dialog box