Roles in Microsoft Access Databases

Articles Index

Course Listing

Free Catalog

Request Information

What's New

View related PowerPoint slides on Slideshare

Glossary of Database Terms


Page 1 | Page 2 | About

There is a concept in database models called a role. A role is, well, the role, played by a participant in a relationship. It often comes up with people. We may have only one employees table, yet employees may play several roles: worker, inspector, technical lead, etc. What if we have a table with columns for both inspector and worker? The Microsoft Relationship Designer can't represent this concept the way a more traditional modeling tool such as Visio would. But it can be done.

Example Database


We will use an expanded version of the Hospital Admissions student database for an example. You can download the MS Access database (and others) at

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

Patients(PatNo, fName, lName, BirthDate, address, city, state, zip)

Diagnostics(DiagNo, descr, cost)

Doctors(PhysId, fname, lname, specialty)

Admissions(AdmitNo, PatNo, Diag_Code, Admit_Date, Dischg_Date, CoPay, Admitting_Phys, Referring_Phys)

Database Design

The database design has each one of the above entities with a twist. The Doctors table appears twice. When you add it a second time Access gives it the name Doctors_1. Next time it would be Doctors_2 and so on.

We placed the Doctors table in the relationship designer once for each role that a doctor plays. A doctor could be the referring (say family) physician and another doctor the admitting physician. The same individual doctor may not play both roles but the table will contain doctors playing both roles. If we didn't do it this way we would have separate tables for every role doctors played: consulting, admitting, referring, assisting, etc.

Hospital Admissions Database Design


Let's take a look at queries involving roles. As you can guess, there are a few nuances that need to be taken into account. We'll do it the wrong way first. Then the right way. This will also clarify the concept of roles.


In this first query we'll simply link the admitting and referring physician foreign keys to the doctors table and see what we get. This is the default join generated when you add the admissions and doctors tables to the query designer.

Incorrect Query

As shown below the above query retrieves nothing. How can an admissions record match a doctors record that has two primary key values at the same time? It can't. We need to design this query differently as shown on the next page.

Incorrect Query - Results 

Continue to the correct roles query (page2)...

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 SQL course catalog.


Please send comments and suggestions to WebMaster at

Copyright 2017 D.H.D'Urso & Associates
Laguna Niguel, Orange County, California

 Articles Index | Close Window | Next| Top