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 to data present in another 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 relationship between several items kept in multiple Access tables. You can fetch this option clicking to Database Tools > Relationships.

Example Of Access Database With 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 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 record present in the OrderDetails table with same ProductID.

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

Relationship also prevents data missing issue by saving deleted data from getting out of synch. This is known as referential integrity.

Types of MS Access Relationships

Basically there are three types of MS Access relationships.

One-to-One:

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

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

In case of One-to-Many relationship, table A contains a row which 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 row 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 Create Relationship In Access?

You can create relationships in Access using the Relationships Window.

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

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

Note:

While creating relationships between tables in Access, common fields does 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 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 field (mainly the primary key field) of one table into the common field (which as foreign key) in another table. If you need to drag more than one fields. Then keep pressing the CTRL key, tap to each field, after then drag them all. Now Edit Relationships dialog box pop-up in your screen.

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

How To Create, Edit And Delete MS Access Relationships

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

  1. Hit to the Create option.

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

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 relationship line, whereas the infinity symbol located above thick portion present on other side of line.

How To Edit Table Relationship In Access?

You can make modification 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 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 to 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.

  1. Now on Design tab, go to 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 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.

  1. For changing the relationship, tap to relationship line. When it is selected it appears thicker.
  2. 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.

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

How To Delete Table Relationship In Access?

remark: With the deletion of 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 relationship.

To remove 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 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 table 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 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.

  1. Hit on the relationship line of the relationship which you want to delete. The line of relationship appears thicker after being selected.
  2. Hit the DELETE key.
  3. 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.

 Try The Recommended Option:

Sometime the relationship in Access goes missing or won’t work either due to table corruption or due to entire of your Access database corruption. In that case you should try a professional MS Access Repair  & Recovery Tool. As this recovery software successfully restores every database objects 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. If you have any further query regarding Access database relationships or facing any issue while running relationships in Access  then you can ask it in our FAQ section.

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