Get Quick Idea On How To Create, Edit And Delete MS Access Relationships

Have you ever worked with the Microsoft Access database coolest option “Relationships”? If not then this blog will surely gonna help you a lot.

As this post mainly focuses on providing every pinch of information regarding MS Access Relationships. Apart from this, the blog will also help you in easy learning of different ways to create relationships in Access and how to delete the relationship in Access.

What Are Relationships In Microsoft Access ?

In MS Access relational Database, data contained in one table is associated with data present in other tables. The Access database relationship option is mainly used to link reference information between multiple Access tables.

MS Access relationships window allows you to set relationships between several items kept in multiple Access tables. You can fetch this option by clicking to Database Tools > Relationships.

Example Of MS Access Relationships

Access relationship feature helps you to combine data of two different tables. Every single relationship consists of fields in two tables having corresponding data.

Let’s take an example:

Suppose you have created tables with the names Products and OrderDetails. In these two tables, you have one common field i.e ProductID. So, every record in the Products table has a ProductID which corresponds to the record present in the OrderDetails table with the same ProductID.

When anyone uses a related table in the query, the relationship allows Access to make a selection about, which records of each table need to get combined in the result set.

The relationship also prevents data missing issues by saving deleted data from getting out of synch. This is known as referential integrity.

What Are The Types Of Relationship In MS Access?

Basically, there are three types of MS Access relationships.

One-to-One:

Table A having a row that is exactly the same present in table B and vice versa. In this way, one to one relationships in Access is created

One-to-Many (or Many-to-One)

In the case of a One-to-Many relationship, table A contains a row that has more than one similar row in table B. But when it comes to vice versa a row of table B must contain only one matching row in table A.

Many-to-Many

In many to many relationships, table A can contain many similar rows in table B and vice versa. Well, this kind of relationship can be made using third table commonly known as junction table which keeps lookup data of both tables.

How To Work With Relationships In Access Database?

I have listed down the complete manual steps to set up the relationship in the Access database, but for quick and easy learning you can watch out this video:

How To Create Relationships In Access 2016?

in this section we will discuss how to Create Relationships In Access 2016. Well, you can create relationships in Access using the Relationships Window.

In the Microsoft Access Relationships window, add tables that you want to crosslink. After then drag the field to link them from one table to another.

Another method is to drag a field from the Field List pane to the table datasheet.

Note:

While creating relationships between tables in Access, common fields do not need to have the same name.  But the field must contain the same data type.

Create A Table Relationship By Using The Relationships Window

• In your MS Access application window, go to Database Tools  Now, from the Relationships section, tap to the Relationships icon.

• Tapping to this Relationships icon will automatically open up “Show Table” dialog box on your screen. If this dialog box won’t come automatically. After then go to the Design tab>Relationships section and tap to the Show Table option. In this Show Table dialog box, you can see all tables and queries used in your particular database. If you want to see only Access tables then click to the Tables tab. In order to see only Access queries, tap to Queries tab. To see all tables and queries tap to Both tab.

• Make a selection of either one or more queries/tables after then tap to the Add After completing up the work of adding tables and queries into the Relationships document tab, hit the Close option.

• Now you need to drag the field (mainly the primary key field) of one table into the common field (which a foreign key) in another table. If you need to drag more than one field. Then keep pressing the CTRL key, tap to each field, after then drag them all. Now Edit Relationships dialog box pop-up on your screen.

• You need to verify for the field names that all the shown field names are actually common fields for relationships. If in case the field name won’t match or it is incorrect, then tap to the field name and make a selection of the right field from the list.
  For enforcing referential integrity for this new relationship, choose the Enforce Referential Integrity check box.

To catch more information on referential integrity, check out our blog on Enforce Referential Integrity.

6. Hit the Create option.

MS Access will make a relationship line between two tables. If you have chosen the Enforce Referential Integrity check box, the line will seem to be thicker at each of the ends.

Additionally, if again you have selected the check box of Enforce Referential Integrity, then the number 1 will appears above the thick portion present on one side of the relationship line, whereas the infinity symbol located above the thick portion present on another side of the line.

How To Edit Relationship In Access?

You can make modifications in the relationship by choosing it in the relationship window and then make editing of it.

1. Correctly position the cursor on the relationship line and then tap to the line to make the selection of it. When the relationship line is selected well, it appears thicker.
2. Double click the selected relationship line.

–or else-

Go to the design tab, and from the Tools group, tap to the Edit Relationships option.

Hitting on this option will open the Edit Relationships dialog box.

Open The Edit Relationships Dialog Box

1. Go to the database tools tab, and from the Relationships group, tap the Relationships icon.

This will open up the Relationships window on your screen.

If you are opening the relationships windows for the first time, then it will pop-up a Show Table dialog box on your screen. If this dialog box appears, hit on the Close option.

2. Now on the Design tab, go to the Relationships group and hit the All Relationships option.

You will see all the tables are get listed along with their relationships and relationship lines.

Note:

The hidden tables and their relationship won’t appear until Show Hidden Objects is been chosen in the Navigation Options dialog box.

For this, you need to uncheck the hidden check box option of the particular table present within the table’s Properties dialog box.

3. For changing the relationship, tap into the relationship line. When it is selected it appears thicker.
4. Make double tap on the relationship line.

-or-

Go to the Design tab and from the Tools group, hit on the Edit Relationships option.

This will open up the Edit Relationships dialog box.

5. Do whatever change you want to do, and then click to the OK

How To Delete Relationship In Access?

Remark: With the deletion of the relationship, the referential integrity support for that specific relationship is also been removed. After then the Access will not prevent the automatic creation of records on many sides of the relationship.

To remove a relationship from any table, you need to delete the relationship line appearing in the Relationships window.

Place your cursor pointing on the relationship line, and hit the line. After tapping on the relationship line, it will appear thicker if it is selected. Keeping the relationship line selected, hit the DELETE option.

1. Firstly, you need to go to the Database Tools tab, and then from the Relationships group, hit the Relationships
2. Now go to the Design tab, and in the Relationships section, tap to the All Relationships after then all the tables along with their relationships and relationship lines are displayed.

Note:

The hidden tables and their relationship won’t appear until Show Hidden Objects is been chosen in the Navigation Options dialog box.

For this, you need to un-check the hidden check box option of the particular table present within the table’s Properties dialog box.

3. Hit on the relationship line of the relationship which you want to delete. The line of relationship appears thicker after being selected.
4. Hit the DELETE key.
5. After then the Access will display the pop-up message like: “Are you sure you want to permanently delete the selected relationship from your database?”. when this confirmation message comes to your screen, tap to the Yes option.

So, try out the above steps to delete relationship in Access.

Try The Recommended Option:

Sometimes the relationship in Access goes missing or won’t work either due to table corruption or due to the entire of your Access database corruption. In that case, you should try a professional MS Access Repair  & Recovery Tool. This recovery software successfully restores every database object like queries, forms, reports, macros, relations, the referential integrity, modules, field properties, tables, relations, and absolutely all field types including Memo, OLE from the corrupted MDB and ACCDB files.

Conclusion:

So, now you have got enough idea on how to create, edit or delete relationship in Access. To check this up you can query data across any of the two tables and get meaningful results from it.