How to Link Multiple Tables in Access 2016

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:

  1. Clients
  2. Orders
  3. Products
  4. Shipping

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:

 

Table in MS Access

 

(2) Orders Table:

 

MS Access Table

 

(3) Products Table:

 

Access Table

 

(4) Shipping Table:

 

Table in MS Access

 

Once you created those 4 tables, they would appear under the All Access Objects menu on the left-hand-side of your screen:

 

Link Multiple Tables in Access 2016

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:

 

Query Design in Access 2016

 

(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.

 

How to Link Multiple Tables in Access 2016

 

(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’

 

How to Link Multiple Tables in MS Access 2016

 

(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.

 

Link Multiple Tables in Access 2016

 

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)

 

Add fields in Access 2016

 

(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:

 

How to Link Multiple Tables in Access

 

(3) Here is the result that you’ll get:

 

Linking tables in Access

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

 

Save query in Access 2016

 

(3) Rename your query. Here I chose to rename the query as ‘Tracking_Sales’

 

Save query in Access 2016

 

(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:

 

Run Query in MS Access