How to Link Tables in Access using an Inner Join

There are few ways to link tables in Access. In this short guide, I’ll show you how to link tables in Access using an Inner Join.

In general, an inner join can be used to obtain all the records (i.e., rows) from the linked tables, where a common value exists in both of the tables. 

Steps to Link Tables in Access using an Inner Join

Step 1: Create the Tables

To start, create the tables that you’d like to link in Access.

For example, I created two tables that contain information about Clients and Shipping.

This is how the Clients table would look like in Access:

Table in Access

While this is how the Shipping table would look like:

Example of table

The ultimate goal is to link the Clients table to the Shipping table using the Client ID field, which exists in both of the tables.

Step 2: Link the Tables in Access

To link the tables in Access, you’ll first need to navigate to the Create tab, and then click on Query Design:

Query Design

Next, double-click on each of the tables (i.e., the Clients and Shipping tables) under the ‘Show Table’ box. Press ‘Close’ once you’re done:

Link Tables in Access using Joins

You’ll now see the two tables with their respective fields:

Link Tables in Access 2016 using an Inner Join

Drag the ‘Client ID’ field from the Clients table, and place it on top of the ‘Client ID’ field in the Shipping table:

How to Link Tables in Access using Joins

You just linked the Clients table to the Shipping table using the common ‘Client ID’ field.

Step 3: Select the Fields to Display

You can now select the fields that you’d like to display from your linked tables. To do so, double-click on your desired fields for each of your tables.

For our example, let’s double-click on the following fields:

  • The ‘Client First Name’ and the ‘Client Last Name’ from the Clients table
  • The ‘Shipping Address’ from the Shipping table

How to Link Tables in Access using an Inner Join

Finally, click on Run in order to display the results:

How to Link Tables in Access

You should now see the 3 fields from the linked tables:

Query results

This type of a linkage is an inner join linkage.

Deeper Dive into Joins in Access

What does an inner join means in the context of our example?

If you look closely at the original Clients table under step-1, you’ll notice that there are 5 clients (with 5 unique Client IDs):

Table in Access

However, the Shipping table only contains 4 unique Client IDs. The Client ID ‘555’ doesn’t exist in the Shipping table:

Example of table

Hence, this record will not be displayed when linking the two tables using an Inner Join (as ‘555’ is not a common value in both of the tables).

But what if you want to display all the records from the Clients table, even if there are no common values in the Shipping table?

You can accomplish this goal by following the steps below:

First, switch back to the Design View by clicking on View, and then selecting the Design View option:

How to Link Tables in Access using an Inner JoinIf you double-click on the line that connects the two tables, the ‘Join Properties’ box would appear.

The first option represents the Inner Join. This means that you’ll only get the rows where the joined fields from both tables are equal:

How to Link Tables in Access using an Inner Join

To display the record that is associated with the Client ID of ‘555’, you’ll need to choose the second option to include all records from the ‘Clients’ table, but only those records from the ‘Shipping’ table where the joined fields are equal:

Link Tables in Access using Joins

Once you selected the second option, run the query and you would get these results:

Example of data

You’ll now see the 5th client from the ‘Clients’ table.

However, the Shipping Address associated with this client will be blank, as there is no shipping order associated with that client in the ‘Shipping’ table.