Joining Tables Automatically

See Also   Tasks

When you add two or more tables to a query, the Query Designer attempts to determine if they are related. If they are, the Query Designer automatically puts join lines between the input source windows for the tables.

The Query Designer will recognize tables as joined if:

• The database contains information that specifies that the tables are related.
  
  
• If two columns, one in each table, have the same name and data type. The column must be a primary key in at least one of the tables. For example, if you add employee and jobs tables, if the job_id column is the primary key in the jobs table, and if each table has a column called job_id with the same data type, the Query Designer will automatically join the tables.

  Note   The Query Designer will create only one join based on columns with the same name and data type. If more than one join is possible, the Query Designer stops after creating a join based on the first set of matching columns that it finds.

• The Query Designer detects that a search condition (a WHERE clause) is actually a join condition. For example, you might add the tables employee and jobs, then create a search condition that searches for the same value in the job_id column of both tables. When you do, the Query Designer detects that the search condition results in a join, and then creates a join condition based on the search condition.

If the Query Designer has created a join that is not suitable to your query, you can modify the join or remove it. For details, see Modifying Join Operators and Removing Joins.

If the Query Designer does not automatically join the tables in your query, you can create a join yourself. For details, see Joining Tables Manually.