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:
While your Shipping table should look like this:
Steps to Link Tables in Access 2016 using an Inner Join
(1) First, go to the ‘Create’ tab. Then, press the Query Design icon:
(2) Now, double click on each of your tables (i.e., Clients and Shipping tables) under the ‘Show Table’ box:
(3) You’ll now see both of your tables with their respective fields:
(4) Drag the field ‘Client ID’ from the Clients table, and place it on top of the ‘Client ID’ field in the Shipping table:
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:
(6) To display the fields of your linked tables, press the ‘Run’ icon:
(7) Congratulation, you just linked the Clients table with the Shipping table. The results should look like this:
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:
(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:
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:
If you press the ‘Run’ icon, the result should look like this:
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.