Electronic Health Records

5 people have rated this page an average of 3.4 out of five stars.  See their suggestions and questions.
	Listen to lecture on difference of paper and electronic health records by Bruce Slater, MD, MPH
	See a video on VISTA office electronic health record.
Objectives Invited Lecture Presentations Analyze data Recently Asked Questions Suggested improvements More Minute evaluation

Objectives

1. To become more familiar with use of databases in electronic medical records

2. To articulate advantages of electronic health record compared to paper record

3. To understand the structure of Veteran Administration's VISTA Electronic Health Record

4. Continue to work with use of queries in databases

Invited Lecture by Bruce Slater, MD, MPH

1. Read about differences between paper and electronic records. 

2. Listen to a lecture on paper and electronic health records.   

3. See the presentation without voice.

Veteran Administration's Electronic Health Record

1. Read introduction to VistA (you do not need to answer questions at the end of the introduction).
2. See VistA demo (requires internet explorer).

3. Read about the functionality within VISTA.  More details can be found at the Vista Documentation Library . 

Invited Lecture by Ken Rubin

Presentations

There are three several presentations for this lecture:

1. Slides for Ken Rubin's lecture.

2. Listen to a lecture on paper and electronic health records by Bruce Slater, MD.    See the presentation without voice.

Analyze Data

The following questions refer to the database you set up in the previous lecture.  If you recall you imported data from the following files into four tables.  From ptid.xls to Patients, from claims.xls to Claims, from icd.xls to ICD9, from cpt.xls to CPT table. 

1. Identify the top 5 most frequent diagnoses in claims table by their names.  See it done.
2. Create a crosstab table where the rows contain the top 5 diagnosis names, the columns are top 5 procedure codes and the cell values are the number of people.  See it done. 
3. Using the following three tables:
    

   Table:  Frequency
   List in order of frequency initials of people you see frequently?
   Frequency of contact	Initials
   First	AW
   Second	JH
   Third	LW

    

   Table:  Importance
   List in order initials of people who are important to you.
   Importance of contact	Initials
   First	AW
   Second	LK
   Third	LW
   Fourth	GW

    

   Table:  Orientation
   For each person you have listed, indicate if they are likely to be a positive or negative influence on you when it comes to drug use:
   Initial	Orientation towards drug use
   AW	Positive
   LK	Negative
   LW	Positive
   GW	Positive
   JH	Negative

   Create the following linked queries and forms:

   1. (Optional) Set up three forms (one for each table).  Type the questions on top of the table as the key question in the form.  Arrange the form to collect the information needed in the table.    See how to create forms. 
   2. Enter the data in the corresponding three tables. 
   3. Calculate a query that assigns to the people not listed in the frequency or importance tables, the last available rank.   For example, initials GW which is not listed in the table "Frequency" should be given rank 4 as there are three others listed.     See it done. 
   4. Create a query that calculates a new field called weight.  Calculate a new field called FrequencyWeight equal to 1 divided by the rank for data in the table Frequency.  Calculate a new field called ImportanceWeight equal to 1 divided by the rank for data in the table Importance.  See it done. 
   5. For the tables Frequency and Importance, create a query that calculates a new field called Standardized weight.  Standardized weight for frequency, SWF, is equal to the FrequencyWeight divided by the sum of the FrequencyWeights.   Standardized weight for importance, SWI, is equal to the ImportanceWeight divided by the sum of the ImportanceWeights.   See it done. 
   6. Create a query that, for each initial, multiplies the standardized weight for frequency of contacts, SWF,  with standardized weight for importance of contact, SWI, and the orientation of contact (0 if negative and 1 otherwise).  Sum the resulting finding across initials.     See it done. 
      
      This exercise is based on a recent paper on how to analyze social networks of drug users.  See a web page organized to collect the Orientation of Social Support tool, information used in this assignment. 

Recently Asked Questions

Question: I am trying to get through question 1. Maybe it is because I have MS Access 2007 but when you make the query table, mine does not show up in my navigation pane. Also, when I try to sort my query results it says "Syntax error in query. Incomplete query cause." I know this has nothing to do with how I imported the tables because I followed the import table video just to make sure. Also, I know I followed the query exactly on the video.   Answer: It works fine on my computer, and I am also using Access 2007. Try to use the sort option before running the query (in design view).  This question was asked on 10/31/2008 7:46:18 PM and answered on  11/3/2008 4:03:33 PM.

Question: Question 3 Part D - I am getting an #ERROR message when I attempt to calculate the FrequencyWeight. Are we supposed to enter figures into our tables in order to calculate the frequency weight?  Answer: Not sure what might be causing this. Please contact Vikas and maybe he can help you understand the problem you are facing.  This question was asked on 11/27/2006 5:51:44 AM and answered on  11/27/2006 9:08:47 AM.

Question: . Standardize the cost of diagnosis SCi so that it ranges from 0 to 1. Use the following formula: SC1= (Average cost of diagnosis-Minimum cost of any diagnosis)/(Maximum cost of any diagnosis-Minimum cost of any diagnosis) I cannot figure out why nothing comes up in the Standardized Cost column. I have redone the entire process at least four times and have caught a couple errors, but it still is not working. Please help.   Answer: Please ask for assistance through Vikas. I cannot imagine what might be going wrong. It would be more helpful if someone looks at your work and then tries to find the error.  This question was asked on 11/21/2006 10:17:08 PM and answered on  11/22/2006 11:17:37 AM.

Question: On Lesson 10 Question 3 Part E, I cannot get the Sum of Frequency to come up with a single number, instead I get three rows of numbers for some reason. Is there a small step somewhere that I may be missing? I have gone through your video demonstration at least four times and keep coming up with the same results. I will try to attach the file.   Answer: The sum is calculated around a grouping of the data. Check to see if there are three different groups involved. Do this by looking at the data. The conduct each step and re-look at the data and find out why three different groups may be involved. You can also ask for help from Vikas  This question was asked on 11/21/2006 10:07:49 PM and answered on  11/22/2006 11:15:59 AM.

Question: 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.

Question: 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.

Question: For question #2, would you like us to provide screen shots of verification of the data as is shown in the last minute or so of the corresponding presentation?  Answer: Yes that would be helpful.  This question was asked on 11/5/2005 8:19:22 PM and answered on  11/6/2005 10:25:21 AM.

Question: 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.

Question: 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.

Question: What is OTAF? Do we require to submit a claim with medical records when the secondary insurance say OTAF max applied?  Answer: I do not know the answer to this question. You might find it useful to search the web for the answer to this question.  This question was asked on 6/30/2005 5:49:09 AM and answered on  7/30/2005 10:03:06 AM.

Question: Are you going to put in a "See It done" For question 3??? I sure could use some help with it! I have created the tables but am having difficulty with the null part of the query. Thank you   Answer: Will do so, have run into some technical problems  This question was asked on 4/12/2005 7:43:21 PM and answered on  4/13/2005 2:06:05 PM.

Question: In question 3 do we start by creating table for Frequency, Importance and Orientation? Would the Frequency table for example contain: ID as Primary key and autonumber, FrequencyOf Contact with possibly creating a Contact Table for 1st, 2nd, 3rd, etc to be used by the other tables as well, Initials of Contact field?  Answer: Yes, you are correct. Please start with creating tables called Frequency, Importance and Orientation. We will post a video for how to solve this section on Monday.  This question was asked on 4/10/2005 12:23:37 PM and answered on  4/11/2005 8:39:27 AM.

Question: Spring 2005 709 this class is Lesson 10 not Lesson 9 as listed in the drop down box under Select the topic of the question. Actually the drop down box has the incorrect lesson number for most of the topics as on the Spring 2005 709 class syllabus states.When the Get Answers for EMR is viewed it appears the questions and answers relate to the lecture Tables and Records not EMR.Is that true?  Answer: You are correct. It started from topic number 0 and therefore all topics were off by 1. I have deleted the numbering so that there would not be an opportunity for an error. I do believe that the answers are posted correctly, however.   This question was asked on 4/10/2005 12:11:10 PM and answered on  4/11/2005 8:49:51 AM.

Question: In regard to question 2, when I went to view the "see it done" nothing comes up.  Answer: I am aware of this and will correct the problem before end of today.  This question was asked on 4/8/2005 9:05:19 AM and answered on  4/8/2005 12:18:01 PM.

Question: 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.

Question: 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.

Question: 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.

Question: 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.

Question: 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.