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:
While this is how the Shipping table would look like:
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:
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:
You’ll now see the two tables with their respective fields:
Drag the ‘Client ID’ field from the Clients table, and place it on top of the ‘Client ID’ field in the Shipping table:
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
Finally, click on Run in order to display the results:
You should now see the 3 fields from the linked tables:
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):
However, the Shipping table only contains 4 unique Client IDs. The Client ID ‘555’ doesn’t exist in the Shipping 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:
If 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:
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:
Once you selected the second option, run the query and you would get these results:
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.