How to Create a Microsoft Access Relationship Design (Step-by-Step)

Articles Index

Course Listing

Free Catalog

Request Information

What's New

Page 1 | Page 2 | Page 3 | Page 4

About

This article explains how to use the Microsoft Access Relationship Designer to diagram a database. In Microsoft Access parlance the relationships created between the tables in the designer are called permanent links. Those created in the query designer for a query are called transient links. However, the query designer will automatically use the links you create in the Relationship Designer if you have set them up (as we will do in this article).

Background

First, why would you want to diagram your database? Three reasons:

  • implement referential integrity
  • implement relationships between columns with different names
  • show pictorially how the data in your database is organized.

That said, it is not strictly necessary to implement the relationships in the designer if you are willing to give up the above benefits.

For our example we will build an Access Relationship Design for our Factory2000Plus extension database. There are four tables: employees, employee benefits, work orders and labor. Along the way we will look at creating both one-to-one and one-to-many relationships. Lastly, we will experiment with join properties and removing referential integrity.

Database Schema

The schema can be represented in text with a few conventions: bold = required field, underline = primary key, italic = foreign key.

Employees(EmpNo, fName, lName, rate, MgrNo)

Employee_Benefits(EmpNo, insPlan, 401kPlan, savPercent)

Work_Orders(WoNo, descr, cost)

Labor(EmpNo, WoNo, start_date, end_date, hours)

You can download the database at box.net.

Creating the Design

Creating the design can be thought of in two steps.

  • add the tables to the designer
  • link the tables and establish referential integrity

Once we have done that we will go back and make some changes so that we can cover additional aspects of relationships.

  • modify referential integrity
  • modify join properties

Add tables

Open the relationship designer

Click the relationships command on the database tools tab.

Access Ribbons 

This will open the relationship designer with the show tables dialogue. The show tables dialogue allows you to display all the tables or all the queries or both.

Add the tables

Highlight the desired table and click add. Do this for all four tables. Or you can shift click to select all four tables at once. You can also double click the table name to add it.

Show Tables 

Relationship Design before creating relationships

When done the relationship designer should look like this.

Access tables 

You can arrange the tables however you like.  On the next page we will create the relationships (permanent links) between the tables.

Continue to create table relationships...


Submit the form below to be notified of new articles or other Microsoft Access resources made available...

Receive course announcements and news. Sign Up Today!





Email Marketing by VerticalResponse

Learn more about our Microsoft Access courses..

View MS Access course catalog.


Please send comments and suggestions to WebMaster at dhdurso.org

Copyright(2001-2012) D.H.D'Urso & Associates

 Index | Next | Top