How to Link Tables in Access 2016 using an Inner Join

There are few ways to link tables in Access 2016… In this post, I am going to show you how to link tables in Access 2016 using an Inner Join.

An Inner Join is used to obtain all the records (i.e., rows) from the linked tables, where a match exists between the tables.

What do we mean by a ‘match’? A match means that a common value exists in both of the tables. 

Let’s look at the example below to better understated how to link tables in Access using an Inner Join.

How to Link Tables in Access 2016 using an Inner Join

Suppose you have a business that sells office supplies.

If your client data is stored on a separate table, from your shipping data, you may wish to link this information together.

You can then link your ‘Clients’ table with you ‘Shipping’ table.

Before we start, you’ll need to create tables in Access; one table for ‘Clients’ and one table for ‘Shipping’.

Your Clients table should look like this in Access:

 

Table in MS Access

 

While your Shipping table should look like this:

 

Table in MS Access 2016

Steps to Link Tables in Access 2016 using an Inner Join

(1) First, go to the ‘Create’ tab. Then, press the Query Design icon:

 

Link Tables in Access 2016 using an Inner Join

 

(2) Now, double click on each of your tables (i.e., Clients and Shipping tables) under the ‘Show Table’ box:

 

Show Table box in Access 2016

 

(3) You’ll now see both of your tables with their respective fields:

 

How to Link Tables in Access 2016 using an Inner Join

 

(4) Drag the field ‘Client ID’ 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 an Inner Join

 

You have now linked the table Clients with the table Shipping using a common field ‘Client ID’

(5) Select the fields you want to display in your linked tables. You can do that by double-clicking on your desired fields from each of your tables.

Here, I chose to display the fields ‘Client First Name’ and ‘Client Last Name’ from the clients table. While I picked the field ‘Shipping Address’ from the Shipping table:

 

Select the fields you want to appear in your linked tables – Access 2016

 

(6) To display the fields of your linked tables, press the ‘Run’ icon:

 

Run Query in Access 2016

 

(7) Congratulation, you just linked the Clients table with the Shipping table. The results should look like this:

 

Query results in MS Access

Deeper Dive into Joins in Access 2016

(1) You can switch back to the Design View by pressing the ‘View’ icon, and then selecting Design View:

 

Switch to the Design View in Access 2016

 

(2) If you double-click the line that connects the two table, the ‘Join Properties’ box will 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:

 

Joining properties in Access 2016

 

What does it means to you?

If you look closely at the original Clients table at the top of this post, you’ll notice that there are 5 clients (with 5 unique Client IDs).

However, the Shipping table only contains 4 unique Client IDs. The client ID ‘555’ doesn’t exists in the Shipping table (it only exists in the Clients table), as there is no shipping order associated with that client.

Hence, this record will not be displayed when linking the two tables using the first option of an Inner Join.

You can still display this record by choosing the second option. This option represents a Left Join in our case. It will include all records from the ‘Clients’ table, but only those records from the ‘Shipping’ table where the joined fields are equal:

 

Left Join in Access 2016

 

If you press the ‘Run’ icon, the result should look like this:

 

MS Access Query

 

You’ll now see the 5th client that comes 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.