Skip to content
Step by Step Internet 馃寪 Guides for learning to surf the Net

How to relate tables in a Microsoft Access database fast and easy? Step by step guide

Could you save the data of your company, university or business in separate tables in the same database. For example, for an insurance company it would work to have a table with customers and another with the claims that occurred.

However, with Microsoft Access you will be able relate the information from several tables so that you save time and find the information you need without any type of error.

To achieve this, it is necessary that you know how to handle the tool that it offers you Microsoft Access so you can make the most of it. In this post we will explain how to do it. Check out.

What is the use of relating tables in Microsoft Access? Main advantages

When you link and relate Access tables you will be able obtain the information without having to visualize each of the tables in which the data you need is found. That is, you will have access to this data with a few steps which will save time, do not make mistakes and improve the efficiency of the results of your task.

This will be possible, since in these relationships you can also extract the data that will be important to you and which will not. This will allow you to eliminate data redundancy and duplication, making queries a perfect analysis tool. You will also be able to convert an extensive database into something more manageable for the user.

What to consider before relating tables in Access?

As Access is a relational system, there are at least 3 kinds of linkages. You have the relationship at your disposal One to One, One to Many or Many to Many, being the one that you will use the most One to Many in which there is a primary and a secondary table. This means that Access allows you to set a record in the primary table associated with several in the secondary table.

But you must bear in mind that the records of the secondary table can only have one record of the primary table. You also need to consider before relating tables in Access the indication of common fields between primary and secondary table. These must have the same parameters or values ​​for the relationship to occur.

Learn step by step how to relate tables in Access

Learn step by step how to relate tables in Access

With your open database and two or more tables you can start to establish relationships. Since the relationship One to Many It is the most used we will take this as an example. There are several ways to access the function Relations, one of the simplest is by right clicking on the blank part of the database.

This will open a drop-down menu and your options will include: Relations, which you must select. Another way you have is accessing through the tab File and then clicking on the Information option. You can even select the tab Database tools and press the icon Relations. The icon you should look for is a concept map.

Then, you will have to follow this process:

  • Choose the tool Show table and click on it to view the available tables.
  • Inside the box Show table Click on the Tables tab.
  • Press the key Ctrl and then in the tables you want. Then click on Add.

Now is the time to establish the relationship, where you must remember that there is a main table and a secondary table.

Follow these steps:

  • First you go to the secondary table and right-click on it.
  • In the drop-down menu you choose Table layout to be displayed in mode Design.
  • You need to indicate that there is a relationship with the primary table and for that you will create a new field. It is appropriate that the new field has the same name as the field ID of the main table on which you will base your relationship. For example, if in a main table named Students the field exists ID name then in the secondary table First student there must be a field with the same ID name.
  • Once the field is created, guard modifications and close Design mode.
  • Go back to the Relationships function where you can see The tables.
  • To generate the relationship click on the ID field (Id name in the example) from the first table (Students in the example) dragging it to the ID field (Id name) that you created in the second table (First student in the example).
  • Automatically, after dropping the selected item, a box named Modify relationships.
  • You'll see the two tables plus the relationship type (in this case one-to-many) and then several options below. You must check the boxes Enforce referential integrity, Cascade update related fields and Cascading related records.
  • Select Create and you will see the relationship marked by a thick connecting line between both tables.
  • Save changes before closing the tab Relations.