Suppose that you created multiple tables in Access and now you want to link them together. In this tutorial, I’ll show you how to link multiple tables in Access 2016.
In particular, I’ll use an example to show you how to link the following 4 tables:
Your 4 tables in Access
Let’s say that you created the following 4 tables in Access, and now you want to link them together:
(1) Clients Table:
(2) Orders Table:
(3) Products Table:
(4) Shipping Table:
Once you created those 4 tables, they would appear under the All Access Objects menu on the left-hand-side of your screen:
Steps to Link Multiple Tables in Access 2016
Let’s now look at the steps to link those 4 tables together:
(1) First, go to the ‘Create’ tab
(2) Next, click on the ‘Query Design’ icon:
(3) The ‘Show Table’ box will appear on your screen. This box will allow you to choose the tables that you’d like to link together.
In our example, there are 4 tables that you can add. Simply, select each of those tables and then press ‘Add’ (one-by-one).
Alternatively, you can just double-click on each of those tables to add them in the background.
Once you are done adding all the 4 tables, click on the ‘Close’ button.
(4) Your 4 tables will now appear on the screen as below. Under this screen, you’ll be able to link your tables.
Please note that Access already automatically linked your Orders table with your Products table using the common field: ‘Product ID’
(5) To link the remaining tables together, you’ll need to drag the common fields under the Orders table, and then place them on top of the corresponding fields under the other tables. Specifically:
– Drag the Client ID field under the Orders table onto the Client ID field under the Clients table.
– Drag the Client ID field under the Orders table onto the Client ID field under the Shipping table.
– If not already automatically done by Access, drag the Product ID field under the Orders table onto the Product ID field under the Products table.
You have now linked your 4 tables together. The type of linkage among those 4 tables is known as inner join.
Adding fields across your linked tables
(1) To choose the fields that you want to display (after you linked your tables), simply double-click on your desired fields from each of the 4 tables.
Here, I chose to display the following 6 fields:
- Client First Name (from the Clients table)
- Client Last Name (from the Clients table)
- Units Ordered (from the Orders table)
- Product Price Per Unit (from the Products table)
- Product Name (from the Products table)
- Shipping Address (from the Shipping table)
(2) Finally, click on the ‘Run’ icon to display the result of your new query (consisted of the 4 linked tables) with your desired fields:
(3) Here is the result that you’ll get:
Saving your Query
(1) To save your query (consisted of the 4 linked tables), right click on the ‘Query1’ tab
(2) Press ‘Save’ from the drop-down menu
(3) Rename your query. Here I chose to rename the query as ‘Tracking_Sales’
(4) Finally, you’ll notice that your new query will be saved under the All Access Objects menu (on the left-hand-side of the screen).
To display your new linked data at any time, simply double-click on the ‘Tracking_Sales’ query under the All Access Objects menu: