Electronic Health Records

<% if 0 then %> <% end if %> <% fp_sQry="SELECT * FROM RoundRating WHERE (Topic1 LIKE '%Record%' AND Class = 709 AND Topic1 LIKE '%Medical%')" fp_sDefault="" fp_sNoRecords="Be the first person to rate this page. Click on the minute evaluation option below. " fp_sDataConn="healthsciencedb" fp_iMaxRecords=256 fp_iCommandType=1 fp_iPageSize=1 fp_fTableFormat=False fp_fMenuFormat=False fp_sMenuChoice="CountOfLastOfID" fp_sMenuValue="CountOfLastOfID" fp_sColTypes="&Class=3&CountOfLastOfID=3&AvgRating=5&Topic1=202&" fp_iDisplayCols=3 fp_fCustomQuery=False BOTID=0 fp_iRegion=BOTID %>
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. 
Invited Lecture
What do you know?
Analyze data


  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 about the functionality within VISTA.  More details can be found at the Vista Documentation Library . 

  2. See a video on VISTA electronic health record.  Requires ActiveX software download.

  3. FTP to view Entity Relationship diagrams for the VISTA system

  4. How to create a template on Vista. 

Invited Lecture by Ken Rubin

Ken Rubin is a senior consultant with Electronic Data Systems, Inc. (EDS) focused in the areas of health informatics, enterprise architecture and object technology. Mr. Rubin has been a lead architect supporting the Veterans Health Administration [VHA] for over five years, with a particular emphasis on service architecture, electronic health record interoperability, information modeling, semantics, and standards. The Veterans Health Administration is the largest healthcare provider organization in the United States and among the largest globally. Mr. Rubin has 15 years of IT experience, with almost ten of those years devoted to healthcare.

Mr. Rubin has been active on several major VHA initiatives, including the architectural and design involvement for that organizationís health data repository, reference information model, service architecture, and personal health portal.

Mr. Rubin is an active participant in the standards community, where he is currently a Co-Chair leading a joint initiative between the Health Level Seven (HL7) and the Object Management Group (OMG) standards development organizations. That effort is defining open healthcare service interface standards that are needed to realize national and international interoperable EHR solutions. He is a past co-chair of the HL7 Process Improvement committee, and has offered testimony to the National Committee on Vital and Health Statistics in the areas of personal medical record information standards. Ken Rubin can be contacted at ken.rubin@eds.com or ken.rubin@med.va.gov.


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.

What do you know?

  1. Create a table with the following information:

    ID Diagnosis LOS Date 
    1 490 5 12/5/2010
    2 4900 0 4/6/2010
    3 490 6 5/7/2010
    4 420 9/30/2010
    5 410 9 12/9/2010

  2. Write a query to sort the data in order of date of care. 

  3. Calculate the average length of stay in Table 1.

  4. Calculate the average time between two consecutive diagnoses. 

  5. Calculate the average of diagnoses that have a code that has the digits 490 somewhere in its diagnoses.

  6. Calculate the average length of stay for diagnoses whose length of stay is less than 7

  7. Select diagnosis that occurred after June 1st 2010.

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 usersSee a web page organized to collect the Orientation of Social Support tool, information used in this assignment. 



This page is part of the course on Healthcare Databases, the lecture on Queries and Reports.  Copyright © 2005 Farrokh Alemi, Ph.D. Created on January 9th 2005. Most recent revision 05/26/2018