Access is a relational database program, and relationships are at the core of its robust features and functions. A relationship binds one Access table to another by creating an association between fields with matching data. In the key one-to-many relationship, matching data means that all the values in the field of one table appear at least once in the field of another table. This creates a web of interlocking relationships that greatly strengthens data security and accuracy.

It’s important to distinguish between a relationship and a join, which is similar to a relationship. Both refer to establishing a correspondence between fields in different tables that contain matching data. But relationships are created in the context of tying together tables within a database. In contrast, joins are used specifically to refer to fields with matching data in queries. They are essential for executing multitable queries because joins must be established between tables to retrieve only those records you need. When tables aren’t joined, Access doesn’t know how to associate the data, so it retrieves every record from the tables.

 Innies vs. Outies

There are both inner joinsand outer joins. Inner joins, also known as equi-joins, are by far more common. They retrieve records only when there are equal values (matching data) in the common field in both tables. Thus, if you have customers in one table and orders in another, and a common CustomerID field joins them, you can create a query that matches order data with customer data.

But what if you want to find customers that do not have any orders? That’s when you need an outer join. It retrieves all of the records from one table and only those with matching data from the other. Because all the customers from the customers table will be retrieved, you can see which customers don’t have orders.

Let’s work through an example with these scenarios to see how joins work.

 Create A Query

Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. In the Database window, click the Queries button and then New. With Design View chosen, click OK.

In the Show Table dialog box, choose Customers, press CTRL, and choose Orders. With both names highlighted, click Add, then click Close. Click the Maximize button in the upper-right corner of the window for a better view.

Note that there is already a one-to-many relationship established between the Customers and Orders table. Every Customer ID in the Orders table, which is on the "many” side of the relationship, appears at least once in the Customers ID field of the Customers table. That is a powerful management control to guarantee that only authorized customers can make orders. But note that the reverse isn’t necessarily true: you may indeed have customers in the Customers table who have yet to place any orders.

The one-to-many relationship between the two tables automatically establishes a join between the field lists, which lets you execute a multitable query. If no prior relationship had been established, however, you could have created a join by dragging one CustomerID field on top of the other.

 Find Customers With Orders

Right-click the join line between the field lists and choose Join Properties. In the Join Properties dialog box, you can see that a join exists between the Customers and Orders table through the common CustomerID field. In the join options below, option 1 is selected. By default, Access creates an inner join between the two tables, and so only records with matching data will be included in your query. Click Cancel.

In the Customers field list, double-click CompanyName to add it to the design grid. In the Orders field list, double-click OrderID to add it to the second column. Click the View button.

All companies that have orders are listed, along with their orders. Note in the Status Bar at the bottom of the screen that there are 830 records in the dynaset (a subtable in the database that selects and sorts records based on our question and makes any changes automatically in any underlying tables).

 Find Customers Without Orders

Click View to return to Design view. Right-click the join line between the field lists and choose Join Properties. Choose option 2, which will create an outer join that includes all the customers and only records in orders where there is matching data. Click OK.

Click View. Note that there are 832 records in the dynaset. Our previous query included only customers who had orders, so that must mean two customers have yet to place any orders.

Click View. On the Criteria row of the OrderID column type is null. This will limit our query to only records that are blank in the OrderID field. Click View again, and you will see two customers in the Company Name field. FISSA Fabrica Inter. Salchichas S.A. and Paris specialties have not yet made any orders. You used an outer join to find inactive customers, who may require a call from your sales staff.

 A Final Note

If you like mnemonic devices, think of inner joins as what you use to find what’s "in like the in crowd,” and outer joins to find what’s "outin left field.” Outer joins are popular with auditors because they look for the exception or whatever doesn’t quite fit. If you’ve been happily creating queries for years and have never worried about inner and outer joins, you can continue to ignore them: Access will always choose the inner join you need by default. But it’s nice to know that outer joins are there when you need them.