Access 2010: 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.

clip_image001

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

clip_image002

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.

Advertisement
  • Pingback: MS Access 2010: Create Simple Forms

  • Pingback: MS Access 2010 Relationship & Query Interconnect

  • Yarnu Verma

    Thanks mate, good walk through.. Helped me with a school assignment…

  • Scott Levy

    Thanks for the post. Here’s a tutorial that shows how you can easily build an online database-driven web application with a parent-child table relationship, without codinghttp://blog.caspio.com/web-database/creating-one-to-many-relational-datapages/

  • them

    this document was very useful

  • frank

    But how table relationship could be established in web database in Access 2010? Once you start a web database, the relationship control is gone and the icon of relationship becomes not active.

  • Yuri

    thx dude you helped me a lot ………………..

  • Nala

    IM STILL CONFUSSED