Invited Lecture by Bruce Slater, MD, MPH
Veteran Administration's Electronic Health Record
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 firstname.lastname@example.org or email@example.com.
There are three several presentations for this lecture:
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.
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.
Comment: 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.
Comment: 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.
Comment: 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.
Comment: I found this lecture to be informative and easy to follow. This comment was left on 10/6/2006 6:44:05 AM.
Comment: 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.
Comment: this lecture was self explanatory so it really worked well. This comment was left on 3/8/2005 7:36:14 AM.
Comment: 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.
Comment: 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.
Comment: 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.
Comment: 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.
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 11/27/2007