Building Relationships Among Database Tables

One of the advantages of Relational Database Management System such as Access 2010 is to easily setup tables and relationships with constraints to make them more tightly followed. One feature is being the ability to create and maintain relationships between tables.

To start off with, Launch Access, create tables for defining relationships among them.For Instance, I am using an existing database with tables (primary keys assigned and data types with constraints applied ). Launch Access 2010, navigate to Database Tools on new Access ribbon and click Relationships button.

Access will open Relationships tab with Show Table Dialog box. Select tables, queries or both, and hit Add.

clip image002 Access 2010: Building Relationships Among Database Tables

You will see selected tables/queries small windows with columns names in Relationships window. Now sit back start pondering over the selected tables to relate them properly. In this example I am using only four tables (AddictiveTips Example, Courses, Lecturer, Mark Sheet).

clip_image003

I am now creating one-many-relationship between AddictiveTips Example (Student) and Courses table. Click & hold the primary key of AddictiveTips Example, drag it and drop over course name in small window of Courses. Immediately Edit Relationships dialog window will pop-up. Here you can create new relationship between tables, Specify Join Type, and Enforce Referential Integrity. For demonstration purpose, I am going to ignore all options and hit Create.

clip_image004

You will see one-many relationship created between AddictiveTips Example’s ID and course code.

clip_image005

To create one-one relationship with enforcing referential Integrity, hold AddictiveTips Example’s ID, drag and drop it over Mark Sheet’s Id. Edit Relationship dialog box will appear. Now enable Enforce Referential Integrity (ensure consistency & validates corresponding entries) & Cascade Delete Related Records checkbox (ensures removal of all related data from Mark Sheet when AddictiveTips Example’s ID is deleted ). Click Join Type to open it’s dialog box.

clip_image006

These three options are similar to SQL’s inner join, outer join and right outer join. Select a join type and Hit OK.

clip_image007

Click Create to make 1-1 relationship between AddictiveTips Example’s ID and Mark Sheet’s Id.

clip_image008

This is the simplest form of relationships among tables/queries. It becomes much complex and elusive as it grows.

clip_image009

Relationships make it possible to co-relate and ensure data consistency from table to table. Now you can unleash power of Access 2010 by creating more tables and making relationships with them.

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *