Tables & Records

The objective of this section is to show you how to create a simple Electronic Medical Record using Access database.  The purpose is to create a database that will track patients' diagnosis and treatment.  Providers will put in information about patients diagnosis and treatment after each encounter.

Entities & Attributes

Assume that you need to prepare a database called EMR that contains three entities:  Patients, Providers and Encounters. 

For each of these three entities we need to create separate tables.  Each table will describe the attributes of each of the three entities.  The patient attributes are assumed to be first name, last name, date of birth, address (street name, street number, city, State and zip code) and email.  First name is a string of maximum size 20. Last name is a string of maximum size 50.  Street number and  zip code are integer numbers with no decimals.  Date of birth is a date put in the format  DD MMM YY (e.g. 19 Jan 04).  The possible values for the State are Maryland, Virginia, District of Columbia and other.  Patient's telephone number could be text or number.  A patient ID (auto-number) should be used as the primary key for the table.  

The provider attributes are assumed to be first name (text of size 20), last name (text of size 50), whether they are board certified (a yes/no value), date of hire (in format DD MMM YY), telephone ( text or number) and email (text of size 75). Employer's ID number should be the primary key for the table.

The encounter entity is assumed to have the following attributes:  patient ID, provider ID, diagnosis (text of size 50), treatment (text of size 50) and date of encounter (date entered in the format DD MMM YY).  Each encounter should have its own ID number.  The Figure below shows the relationship between encounter entity and patient and provider entities:

Figure 1:  Relationship Among Access Tables

Patient Table

To make this simple electronic medical record, we start by opening a new databases and naming it EMR.  Next we create the three tables.  We begin with the table for the patients. 

We create the patients table in the design view.  Note that we do not put any spaces in field names.  Our first field name is Firstname.  We select for data type "Text."  In the description we type "Legal first name of the patient."  In the field's property menu we set the size of this field to 20.  We set for caption "First name". 

Our second field name is Lastname.  We select for data type "Text."  In the description we type "Legal last name of the patient."  In the field's property menu we set the size of this field to 50.  We set for caption "Last name". 

Our next field is zipcode.  The data type is number.  The description is the "Minimum 5 digits numerical postal zip code."  In the field properties we set the size to 10.

Our next field is City, set as text type.  In the field properties we set the size to 20.

Our next field is State.  This field has fixed menu items.  To set this up we use a look up wizard.  We can look up the values from a different table or type them in.  We will type in the possible values.  

Figure 2:  Start of Look Up Wizard

 The possible values are Maryland, Virginia, DC, and Other. 

Figure 3:  Entering Fixed Menu Items in Lookup Wizard

Our next field is DOB.  The data type is set as Date.  The description is typed in as "Date of birth of patient."  IN the field property we set the format to Medium Date length.  We set the input mask, i.e. the additional guides for the database users to input the date, to Medium Date.  We need to save the database before the input mask is accepted. 

Figure 4:  Field Properties for Date

Email field is set to text type.  Description includes "Current email of patient."

Our next field is Telephone.  This is a number type.  Because telephones are large numbers, we set the size in the field property to double digit precision.

Our last field is a comment field set to text of size 200 and description of "Free form comment about the patient characteristics."   

We now add a field called ID as an auto-number.  This field is selected to be the primary key.  A primary key as you know is a unique number that identifies all contents of a table.  We could have used the combination of first and last name as the primary key, but people's first and last name may not be unique.  Auto-number solves the problem because now every entry will automatically receive a unique number.

This completes the creation of Patients table.   We save the table and name it Patients.  Before we proceed, we look at the data entry mode of the table and enter 3 rows of data for our table.

Table 1:  Three Rows of Data for Patient Table

Provider Table

The creation of the Provider table follows a similar procedure.  Everything in the table is about providers.  Thus a patient's name does not show here.  Only facts about the provider show here.  We start with creating Firstname as a text of size 20, with the description of "Legal first name of provider."

Our second field name is Lastname.  We select for data type "Text."  In the description we type "Legal last name of the provider."  In the field's property menu we set the size of this field to 50.  We set for caption "Last name".  Note as before that captions can have space but field names cannot.

Our next field is BoardCertified.  This field is entered as a Yes/No type.  The description  is "Whether the provider has passed State boards."

Our next field is Telephone.  This is a number type.  Because telephones are large numbers, we set the size in the field property to double digit precision.  The description includes "Telephone number of provider."

Email field is set to text type.  Description includes "Current email of Provider."

We now add a field called ID.  It contains the employee's unique ID number.  This field is selected to be the primary key. 

This completes the creation of Provider table.   We save the table and name it Providers.  Before we proceed, we look at the data entry mode of the table and enter 3 rows of data for our provider table.

Table 2:  Three Rows of Data for Providers Table

Encounter Table

The last table we would like to create is called Encounters.  This table keeps the data from each patient visit.  We enter as our first field an ID, as an auto-number.  This is described as "Unique ID for the encounter."

Next field is the PatientID, entered as a number of size 20. This is described as "Unique patient ID number." 

Next field is ProviderID, entered as number of size 20 and described as "Unique provider employee number."

Next field is Date, entered as a date and described as "Date of encounter."  The field property is set to Medium Date and the input mask is set to Medium Date. 

Next field is Diagnosis, set to text type and described as "Main reason for the visit."  The size of this field is set to 75 in the field's property window.

Next field is Treatment, set to text and described as "Main treatment provided in the visit."  The size of this field is set to 200.

We save the design of the encounters table and enter following data.

Table 3:  Five Records for Encounters Table

Table Relationships

We are now ready to set the relationship among the tables.  The purpose of relationships is to let the database know how the tables are related to each other so that we can trace information pertaining to one record in a table to another table.  To set the relationship among tables, we select from the database menu, tools and then relationships. 

Figure 5:  Setting Relationships

In the relationships window, if the window for adding tables is not showing, we right click to show tables. 

Figure 6:  Adding tables to relationships

We add the three tables Patients, Providers and Encounters.  We then drag the patient  ID in the encounter table to the ID field in the Patient table.  We also drag the field ProviderID to the field ID in the provider table.   You should see something that looks like the very first figure on this page, Figure 1.

In a normal join of two tables, the values of the field must be present in both tables.  It is possible to join two tables and require that only one field should always be present and if the value of the field has no matching value in the other table, then it will have a null value.  If you carefully click on the join line so that it becomes bold, you can right click and see the option to edit the join properties.  In here you can select join type and set the different types of joins. 

Figure 7:  Three types of Joins

For the time being we select to join the encounter and patient table so that the value of patient ID is exactly the same in the two tables.  One consequences of this join is if there is an encounter with a patient whose ID has not been entered, it will not show in the joined tables.  We will review more of this later when we talk about queries.  

What do you know?

1. In the database EMR, Create the tables and relationships described in this lecture.
2. Revise the database EMR to EMR1 so that diagnosis is no longer a free text field but a field where the values of it are looked up from a table called "Diagnosis."  Enter three rows of data in the table Diagnosis with attributes Diagnosis code, Diagnosis text.
3. Revise the database EMR2 into EMR3 so that a patient could have multiple addresses.  To do so, drop the address fields from Patient table.   Create a table called address, where the attributes are Date, ID (set to primary key), Street name, Street number and zip code.  Make sure the primary key in the table is set as the combination of patient ID and date.  Enter following three rows of data in table Address. 

4. Revise the database EMR3 so that zip codes are used to identify the city and state.  Create a table called zip codes with attributes zip code (set to be primary key), City, and State.  Enter three rows of data. 

Please do not send your databases to the instructor.  Some email programs will automatically delete an email in which a database is attached.   Instead, please submit by email a screen image of the database relationships.  Make sure that you copy the question into the word document and follow each question with an image of the relationships among the tables.   Capture the image of the screen by clicking on control and Print screen key on your keypad.  Then paste it into your word document and trim it using the toolbar for pictures (crop option).  make sure your response is not only correct but also looks good.  Send your email to the instructor or bring your work to class. 

Presentations

You can download a video of how EMR database was organized:  Lecture on Tables & Records.  Viewing this video requires Flash.

Recently Asked Questions

Ask a question and we will answer it within the next 48 hours.  If you have no questions, please review the answer to the questions asked by others: 

Questions: In question 3, you asked us to create a table called address and set the primary key to a combination of patient Id and date, you also asked to add an ID and set it to primary key, and add an address ID to the table patient to link it to the address table, so which design option do you want? create a combination of two fields as the primary key in the address table? or add a separate ID in the address table and add a foreign key in the patient table for address ID?  Answer: Please note that the ID in the address table is not an ID of a person but a number that uniquely identifies a match of a person to an address. So in this sense what this question is asking for oyu to do is to create a table of addresses and peoples. This table needs a primary key, which we call ID. This primary key cannot be the ID of the person or the ID of an address. It is an ID for the combination. Please contact Vikas by email if this answer has not been helpful.  This question was asked on 9/27/2006 11:51:47 AM and answered on 9/28/2006 6:03:06 PM.

Questions: Field cannot be updated is the error I get. This always happens on the many side, whether a one to many or many to many table.   Answer: Are you saying that you went to a site and tried to answer a survey and got this message. The reason for this message is that something in your response (usually use of special chracters or length of response) is causing between mismatch. For example it was expecting text and recieved @ sign. You can write to me about where this mistake has occured and I can investigate it further  This question was asked on 9/26/2006 2:58:38 PM and answered on 9/28/2006 5:57:38 PM.

Questions: When entering the field zipcode.we choose data type number.In the field properties I can't seem to be able to set the size to 10, am I doing something different.  Answer: In access, the size of a number is set in terms of single or double precision but not in terms of a maximum amount (e.g. 10 digits)  This question was asked on 10/14/2005 8:55:16 AM and answered on 10/14/2005 10:40:39 PM.

Questions: In the submission of the assignment, would you like to see the strings of each attribute in each table? If so, is there any easy way to copy and paste the format of each attribute from Access to Word?  Answer: Yes I would like to see the format of fields. You can just put the table in design format and take a screen picture of it  This question was asked on 10/9/2005 5:07:20 PM and answered on 10/10/2005 8:19:35 AM.

Questions: In regard to Questions 3 &4, whould it be helpful to use junction tables to assist in the linkage of the tables?  Answer: Yes, in general, it is helpful to have junction table -- specially when values in one table maybe link to multiple values of another field in a different table.  This question was asked on 3/15/2005 5:41:39 PM and answered on 3/17/2005 12:05:15 AM.

Questions: I understand how to set the format for the date, but what exactly is input mask and why does the database have to be saved for it to be accepted?  Answer: The input mask allows you to set separators (e.g. dash and slash) between date and month and year. It allows you to specify whether the month and day are entered in two digits or in text or in some other fashion.   This question was asked on 3/11/2005 6:34:11 AM and answered on 3/13/2005 4:21:31 PM.

Questions: 1Why do you set number in data type for zipcode and telephone number? I think that the zipcode and telephone number can't be calculated. When I created relationship between tables the program showed sentence "The database engine could not lock table 'Encounters' because it is already in use by another person or process." 2 Why does the sentence appear?   Answer: You could do zipcodes and phone numbers as numbers or text, I prefer numbers for these concepts. The choice is up to you regarding how you plan to use these fields. If you want to find adjacent zip codes, you can do so by cmparing the zipcode numbers to each other. You cannot do so by comparing zipcode text to each other. Regarding your last question, the message means that another program has opened the table encounter. You would need to first close the program that is using the table encounter before you proceed with your work.  This question was asked on 3/7/2005 1:13:02 AM and answered on 3/7/2005 10:03:38 PM.

Questions: I'm not sure how to create a primary key as a combination of 2 fields   Answer: The physical procedure in Access is to highlight both keys by clicking on the rows while holding the shift button down. Then click on the key symbol.  This question was asked on 3/6/2005 12:09:04 PM and answered on 3/6/2005 10:29:51 PM.

Questions: tables for online health care system  Answer: I have added additional information to the question pointing you to appropriate place for the data. See if this helps further.  This question was asked on 2/27/2005 1:53:59 AM and answered on 3/1/2005 8:25:31 AM.

Suggested Changes

In this section, you can review suggestions made by you and others regarding how to improve this lecture. 

Comments: Good Lecture. It was easy to understand and reinforced previous knowledge.  This comment was left on 10/25/2009 5:59:31 PM.

Comments: Questions could use more clarification. Good walk through in the instructional text.  This comment was left on 10/4/2009 4:38:52 PM.

Comments: very detailed lecture and the pictures of the database helped alot.  This comment was left on 11/26/2008 5:54:52 PM.

Comments: There were some inconsistencies between the video and how Access actually works.  This comment was left on 11/6/2008 11:11:34 PM.

Comments: I liked the step by step instructions on how to create an EMR.  This comment was left on 10/3/2008 2:51:54 PM.

Comments: This lecture was easy to understand  This comment was left on 9/29/2008 8:26:21 AM.

Comments: I found this lecture to be informative and easy to follow.  This comment was left on 10/6/2006 6:44:05 AM.

Comments: When I tried to open Dr. Atherton's lecture I received the message"the page cannot be found". Has it been removed or is there another way I can access the lecture? Also while the basic steps were clear the later more complicated steps such as two primary key indicator steps were not.  This comment was left on 10/1/2006 10:43:13 AM.

Comments: The demonstration of setting up the tables with the steps of specifying properties, options, and descriptions was very helpful. Seeing the steps especially. Seeing how to make corrections, changes and moves helped, too.   This comment was left on 9/30/2006 10:08:11 AM.

Comments: the step by step walk through of how to create the different tables  This comment was left on 5/7/2006 3:38:36 PM.

Comments: This lecuture was easy, and my suggestion would be to combine this lectuer with the Normalization lecture, and dedicate the normalization lecture to talk about the mistakes and troubles we had with our "what do you know", and to talk about normalization more in depth. The narrated lectures for all of the lectures were really really helpful, pleas keep them for the semesters to come, they are worth the effort put into them. I have rated this lecture as 4 only because I believe that it should be combined with the normalization lecture.  This comment was left on 5/4/2005 11:22:44 PM.

Comments: The homework was fun, but getting graded down when you did not put in your own data (even when the question does not ask for your own data) or not having a question answered when asked does not help   This comment was left on 3/16/2005 6:50:35 PM.

Comments: I hope I did this homework correctly as I enjoyed it. Some detail is missing in the presentation - like how to create a combination key, but I found it in someone else's question. Also, how to save an Access data base would be helpful. Thank you  This comment was left on 3/14/2005 5:37:30 PM.

Comments: This was a straight forward lecture. It helped me grasp the basics of creating tables and recrods.  This comment was left on 3/11/2005 1:40:25 PM.

Comments: This lecture was the best one so far. It actually walked you threw the assignment. Only thing that it missed was the encounters database, but I was able to figure that out because of the other tables.  This comment was left on 3/11/2005 11:12:55 AM.

Comments: this lecture was self explanatory so it really worked well.  This comment was left on 3/8/2005 7:36:14 AM.

Comments: This was a brief lecture, it really helped that we did the homework of ER before tihs lecture, I had a better handle on how to create tables after doing so the assignement 5, and I could follow the lecture better.   This comment was left on 3/7/2005 8:05:46 AM.

More

1. Microsoft tutorial on Tables - What they are and how they work
2. Richard Holowczak (City University of New York) Microsoft Access Tutorial
3. Read the translation of this lecture into Arabic.