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.
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).
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.
You will see one-many relationship created between AddictiveTips Example’s ID and course code.
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.
These three options are similar to SQL’s inner join, outer join and right outer join. Select a join type and Hit OK.
Click Create to make 1-1 relationship between AddictiveTips Example’s ID and Mark Sheet’s Id.
This is the simplest form of relationships among tables/queries. It becomes much complex and elusive as it grows.
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.