1-866-245-5224 sales@keystonelearning.com

Access 2003: Query Joins

Access 2003: Query Joins

A query join is a temporary relationship that you create between two tables in a query that don’t already have an existing relationship. Tables joined in a query are related ONLY within that query – no permanent relationship is created. Permanent relationships can only be created in the Relationships window.

There are two basic types of joins that we’re going to cover:

     
  • Inner Join – Selects only those records from both tables that have matching values (default join type). This is the most common type of join. Inner joins combine records from two tables whenever there are matching values in a field common to both tables.
     
  •  
  • Outer Join – Selects all the records from one table and only those records in the second table that have matching values in the joined field.
     
  •  
  • Left Outer Join - Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.
     
  •  
  • Right Outer Join - Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.
     

Inner Joins

Inner joins are not only the most common type of join, they are also the default join type in Access. An Inner Join tells a query that rows from one of the joined tables correspond to rows in the other table, on the basis of the data in the joined fields. When you run a query with an inner join, only records where a common value exists in both of the joined tables will be included in the query results.

Typically, you don’t need to do anything to use an inner join especially if you have already created relationships and enforced referential integrity.

But, even if you haven’t created relationships, Access automatically creates inner joins if you add two tables to a query and those tables each have a field with the same or compatible data type and one of the join fields is a primary key.

When you have two fields that will contain the same data, you want to make sure those fields have the same name, same size and same data type. These characteristics help Access make an Inner Join for you even if you haven’t yet set your relationships.

Figure 46. Inner Join

If a query doesn’t automatically create an Inner Join for you, you can create one between two tables by dragging the field from the first table and dropping it on top of the corresponding field in the second table.

Outer Joins

Outer joins tell a query that although some of the rows on both sides of the join correspond exactly, the query should include all of the rows from one table, and also those rows from the other table that share a common value on both sides of the join.

There are two versions of an Outer Join – the Left Outer Join and the Right Outer Join.

Left Outer Join

In a left outer join, the query includes all of the rows from the first (or, leftmost) table and only those rows from the other (or, rightmost) table where the joining field contains values common to both tables.

     
  1. In Query Design view, drag to create an Inner Join
     
  2.  
  3. Double-click the join line
     

Figure 47. Join Properties Dialog Box

   3.      Choose Option #2

   4.      Click OK

 

Figure 48. Left Outer Join

Right Outer Join

In a right outer join, the query includes all of the rows from the second (or, rightmost) table and only those rows from the other (or, leftmost) table where the joining field contains values common to both tables.

 In Query Design view, drag to create an Inner Join

     
  1. Double-click the join line
     

Figure 49. Join Properties Dialog Box

    2.      Choose Option #3

    3.      Click OK

 

Figure 50. Right Outer Join

Removing a Join

If you create a join by mistake, for example, a join between two fields that have dissimilar data types, you can delete it.

     
  1. Select the Join line you want to remove
     
  2.  
  3. Click the Delete command in the Records group on the Home tab