MedicalRecords
MedicalRecords is a multiuser, personal, integrated medical information system that facilitates the storage, organization and retrieval of medical information that may be obtained from a variety of sources including physician offices, clinical laboratories and medical centers.
It uses a Microsoft Access 2016 front end to create, display and edit forms. Its report generator produces displays and printed information.
SQL Server 2016 is the database manager. Microsoft Excel and Microsoft Word Chart are used for charting functions. Microsoft Excel is used for printing grids.
Data that are downloadable in machine readable format can be transferred electronically to the database. Alternately, the data can be transferred from USB flash drives, CD ROMs or other removable storage media. Printed documents can be entered by scanning to PDF files or other formats. Finally, information may be entered through use of speech recognition or typing.
The integrated medical record data provide an easy means for presenting the information to a specialist or other new care provider, emergency room staff or admitting physicians.
Information can be transmitted to a new care provider in electronic format, printed documents or as attachments to e-mails.
"MedicalRecords is multi-user thus it can handle the storage of data for multiple members of a family (including their pets, if desired). It can also be used in many types of small medical facilities including progressive care living and the delivery of home patient care.
The information can be reviewed in tabular form which can help the patient and health care provider to identify unusual findings or trends. Reminders of follow-up laboratory tests and prescription refills help prevent errors.
Physicians and other medical care providers unfortunately are frequently slow in reporting results to their patients. In instances in which patients have direct access to a physician's office, medical center's or clinical laboratorys database, the delays in reporting information can be avoided.
Finally, the integrated database includes Patient Reports. These are reports that contain lists of a patient's symptoms, health concerns, objective findings known to the patient and a list of questions and needs. After a visit, a patient adds to their report what they understand to be the physical and laboratory findings and the plans for future care.
The reports are shared with the health care providers before and after visits. These steps help to improve communications between patients and physicians and to reduce errors of omission.
The present version of MedicalRecords has thirty-one tables that include demographic information, addresses, telephone numbers, Internet addresses, multiple listings of clinical laboratory data, imaging, documents, billing statements and medications.
The Medications Form links to the National Library of Medicines drug information databases.
Installation
You need to install Office 2016 Pro 32 bit edition or Office
365 Home Edition 32 bit (2016) unless this software is already available on your computer.
You also need to install 10Tecs iGrid ActiveX control. A demo version of the control can be downloaded from:
www.10Tec.com.
The next step is to Unzip MedicalRecords.zip and to then copy AccessSQLDir and SQLDataStorage to the C: drive. Do not copy these directories into a subfile of C:
Next copy PrintiGrid.xlsx to C:\Users\<your logon name>\Documents
You can then start MedicalRecords by left clicking on:
C:\AccessSQLDir\MedicalRecordsSQLDemo\MedicalRecordsTables.accb
For convenience, you can create a link to this file and store it on the desktop.
Right click on the .accb file, and select Send To followed by Desktop
MedicalRecords Demo is a client server program. Microsoft Access 2016 handles the Design, Display and Editing of Form data and the generation of Reports. Microsoft SQL Server 2016 is the database manager.
MedicalRecords can be evaluated by using our SQL Server which is located at rerthird.dyndns.info
Contact RERThird@gmail.com for instructions for installing the program on your own server.
The Zip includes a SQL Server 2016 Restore file. It can only be used with standard SQL Server, not SQL Server Express which requires a different format Restore file.
The Medications Form links to the National Library of Medicines drug information databases.
Data Input
Text, Images, Video, Audio
Medical reports may be available individually or as parts of larger files; for example, the description of a hospitalization. Many medical organizations support patient portals that provide on-line access to these documents, typically in the form of doc, pdf or txt file. Some health care facilities provide access to two or more file types, most often pdf and txt.
Document data can be easily selected and downloaded. The information is then electronically copied from part or all of the files to the patients personal medical information system. A Form provides a synopsis of contents of the stored files. Doc, txt and pdf files are electronically searchable.
The text files can be stored in fields in a database or the database can store hyperlinks that link to data in an external directory. Storage in the database has the advantages of simplicity and use of a databases security system. Hyperlinks can provide access to very large files which can exceed the size limitations of database manager fields.
Images, video, audio and other specialized files are handled in a similar manner.
Paper documents are entered by scanning directly into a computers file directory.
Small amounts of information can be entered by typing or the use of speech recognition technology.
There are only two limitations on what can be processed. File extensions like .txt identify the file type. The data processing system uses these extensions to determine what kind of a reader is required in order to display, print or listen to the information.
Many of the required readers are free and are capable of handling multiple file types.
A second limitation is file size. SQL Server has a binary field size limit of 2 GB.
Linked file sizes are limited only by a computers storage capacity.
Tabular Clinical Laboratory Data
Tabular data can be stored in documents; but, ideally, the data should be stored in a servers table data fields so that there is direct access to the information. For example, individual columns in a table can be used for a test name, the result, its normal range and value type. An example is: Glucose, 91, 74-118, mg/dL. Additional fields are required for unique row identity, patient and laboratory identification, date/time, name and accession codes and other information.
Laboratory server to personal server transfers of data that have the same basic structure is the optimum method of data transmission. This method is probably only currently practical for data transfers between medium to large organizations.
Computer based clinical laboratory data processing systems date from the 1960s. Unfortunately, there are still no universally accepted structure for data storage and interchange. The complexities of the problem can be illustrated by the test result Glucose. The specimen might be blood, urine, CSF or other fluids. The Date/Times of sample collection and analysis are important because results can change over time. Was the sample centrifuged at the point of collection or at the laboratory? Are there transport issues. For example, being in a hot vehicle for an extended period of time without temperature protection. Test result normal limits change with different reagents and different automated analytic systems. They also may change with a patients age, sex, race, fasting status, medications and other factors. Finally, there is the issue of coding errors. The more complex the coding system, the more likely that these errors may occur.
The Logical Observation Identifiers Names and Codes (LOINC) system was named as one of the designated standards for the interchange of clinical laboratory test results in 1999. The code is widely used, but is not a universal standard. Additional considerations are the formats of the database manager tables and how the information is made available for data interchange.
The following experience applies to an example of using LOINC. The exact details of the database structure and the transfer of its information to an Excel file are unknown. Microsoft Access wizards were used to import the information from Excel to Access and to then export it from Access to SQL Server. In the process, Access automatically adds a primary ID field (with Identity) and sequentially numbers the records.
The resultant table becomes part of the MedicalRecords personal, integrated SQL Server database. Its contents can be displayed as individual records or in grid format as laboratory test profiles or aggregates of one or more individual test results.
A third choice is to abstract a txt file that contains the laboratory information in print format. Coding an abstractor is a nuisance, but properly designed programs can be accurate and easy to use. MedicalRecords, Demo Version, includes programs that abstract data from a Quest Chemistry Profile and Veterans Administration Chemistry, Hematology and Urine Profiles and all of the individual test results that have been encountered to date. The following example is based on the U. S. Department of Veterans Affairs (VA) database It can be accessed on-line through the MyHealtheVet software and the "Blue Button" report generator. There are a variety of user information retrieval options including office visit reports and clinical laboratory data.
The VA makes the data available in PDF and TXT format. The TXT format is a series of lines that are delimited by a combination of carriage feed and line feed characters at the end of each line of text. The file sizes are large. There are 1000 lines in a file that contains approximately one months of outpatient laboratory data for a single patient. The following abstract has real data except that the names of the patient, staff and locations have been changed. The abstract is limited to clinical laboratory data. The original report contained no unique patient or Test Name identifiers.
The Lab data are sorted By: Date Specimen Collected (Descending) Lab Test (Alphabetical
Order), then Time Specimen Collected.
EXAMPLE
--------------- MY HEALTHEVET PERSONAL INFORMATION REPORT ---------------
Produced by the VA Blue Button (v12.10)
13 Sep 2015 @ 1104
This summary is a copy of information from your My HealtheVet Personal
Health Record. Your summary may include:
- information that you entered (self-reported)
- information from your VA health record
- your military service information from the department of defense (DoD).
***Note: Your health care team may not have all of the information from
your Personal Health Record unless you share it with them. Contact your
health care team if you have questions about your health information.
Key: Double dashes (--) mean there is no information to display.
Name: Patient, Test Date of Birth: 02 Feb 1950
------------------------ DOWNLOAD REQUEST SUMMARY -----------------------
System Request Date/Time: 13 Sep 2015 @ 1104
File Name: VALabTest.txt
Date Range Selected: 01 Aug 2015 to 13 Sep 2015
Data Types Selected:
VA Laboratory Results: Chemistry/Hematology/Microbiology
--------------------- MY HEALTHEVET ACCOUNT SUMMARY ---------------------
Source: VA
Authentication Status: Authenticated
Authentication Date: 12 Nov 2014
Authentication Facility ID: 1234
Authentication Facility Name: A VA Medical Center
VA Treating Facility Type
-------------------- ------
A VA Medical Center
------------------------- VA LABORATORY RESULTS -------------------------
Source: VA
Last Updated: 13 Sep 2015 @ 1103
Sorted By: Date Specimen Collected (Descending) Lab Test(Alphabetical
Order), then Time Specimen Collected
VA Laboratory Results are available 3 calendar days after they have been
verified. For some tests, results slightly outside the reference range
are not unusual. In addition, not all results are clinically significant.
If you have any questions about your information, please visit the FAQs or
contact your VA health care team.
=========================================================================
Lab Test: Urinalysis Chemical w micro Auto~IQ200
Lab Type: Chemistry/Hematology
Ordering Provider: A Physician
Ordering Location: VAOP
Specimen: Urine (substance)
Date/Time Collected: 08 Sep 2015 @ 1445
Collected Location: VAOP
-------------------------------------------------------------------------
Test Name: APPEARANCE
Result: HAZY
Units: --
Reference Range: (clear)
Interpretation: --
Performing Location: VAOP
Status: Final
----------------------
Test Name: BACTERIA
Result: FEW
Units: --
Reference Range: (none seen)
Interpretation: --
Performing Location: VAOP
Status: Final
END EXAMPLE
The program first searches for the title Name: and Date of Birth: and abstracts the field information. These identifiers are compared with those in the Personal Database to provide a limited confirmation that we have the correct patient.
The program next searches for the Title VA LABORATORY RESULTS
This is followed by a loop that detects the titles Lab Test: and abstracts the name of a test (for example, Urinalysis Chemical w micro Auto~IQ200) and its row number. The program handles test profiles and individual tests. It also processes multiple instances of results that have different dates, but the same panel or individual test names.
Another loop branches sequentially to each of the Lab Test: title names and abstracts contents of the panels and the individual test results.
For example, in the Urinalysis panel, you can start with the Test Name: APPEARANCE: and abstract the Result Cloudy
The process is complicated by Lab Test: and Test Name: not always being identical.
For example, Uric Acid becomes URATE.
Finally, some titles and results may be completely missing. There are no replacement NULLs or blanks which greatly complicates the search process, especially if data are missing in one panel and are present in another panel with the same name, but a different date.
Note that the information formats can change unexpectedly. Also, a schema is not available thus it is necessary for at least one report to contain an example of each test type that needs to be abstracted.
Finally, the abstracted data are written to the appropriate fields in the SQL Server personal database.
MedicalRecords currently accepts a wide range of file types. Clinical laboratory data are acquired through use of a translator that abstracts the desired information and stores it in SQL Server tables.
The Health and Human Services Department issued a final rule 2/6/2014 entitled:
CLIA Program and HIPAA Privacy Rule; Patients' Access to Test Reports
A synopsis is as follows:
Quote
This final rule amends the Clinical Laboratory Improvement Amendments of 1988 (CLIA) regulations to specify that, upon the request of a patient (or the patient's personal representative), laboratories subject to CLIA may provide the patient, the patient's personal representative, or a person designated by the patient, as applicable, with copies of completed test reports that, using the laboratory's authentication process, can be identified as belonging to that patient. Subject to conforming amendments, the final rule retains the existing provisions that require release of test reports only to authorized persons and, if applicable, to the persons responsible for using the test reports and to the laboratory that initially requested the test. In addition, this final rule amends the Health Insurance Portability and Accountability Act of 1996 (HIPAA) Privacy Rule to provide individuals (or their personal representatives) with the right to access test reports directly from laboratories subject to HIPAA (and to direct that copies of those test reports be transmitted to persons or entities designated by the individual) by removing the exceptions for CLIA-certified laboratories and CLIA-exempt laboratories from the provision that provides individuals with the right of access to their protected health information. These changes to the CLIA regulations and the HIPAA Privacy Rule provide individuals with a greater ability to access their health information, empowering them to take a more active role in managing their health and health care.
End Quote
Reference:
https://www.federalregister.gov/articles/2014/02/06/2014-02280/clia-program-and-hipaa-privacy-rule-patients-access-to-test-reports
iGrid/ListView
The clinical laboratory Forms have a command button option that pops-up a Microsoft ListView or a 10Tec iGrid control that shows a listing of all of the laboratory test results for a specific form; for example, the Chemistry_P14 panel. This provides an easy means of comparing the test results.
The contents of the ListView and iGrid controls are printable using Microsofts Excel software.
Program Operations
The program starts with the Search Form. You can search for a patient by entering part of a [Last Name], [First Name, Last Name], [First Name], [Birthdate], [SSN], [Telephone Number] or [Street Address]. For example, in searching for the Last Name Patient one could type Pat followed by an Enter and the system will display a list of all the last names that begin with Pat. Double click on the name of interest and the system will find the demographic information and setup accesses that are limited to this persons database. You may then observe the information that is displayed by the various Forms and Reports by double left clicking on their names; for example, Medications.
You can create a Demographic file for a new patient by clicking on Add New Patient on the Search form. The primary identifier is the SSN. If it is not available, you can use a birthdate in the format YYYYMMDD followed by 0 (numeric zero) to supply a total of nine digits.
The SSN are automatically screened as follows: TRIM eliminates accidentally entered preceding or trailing blanks. REPLACE eliminates imbedded hyphens and blanks. The resultant string is checked to ensure that it contains exactly nine characters and that all of them are numeric.
A new report is created by double left clicking of a Forms New Report button. A unique ID number, the patients Social Security number and a date/time stamp are automatically added to each New Record. The data fields of a Form are automatically bound to the corresponding SQL Server fields so that the Form information is stored at the server.
The Form that is used for Demographic information contains four drop down Combo boxes that display lists of personal and business addresses, telephone numbers, Internet addresses and account data. Four tables are used for this information: [AccountsCards], [Addresses], [InternetAddresses] and [TelephoneNumbers]. Forms with these names are used to enter and display the information. There are separate Report generators for these tables. The individual reports can also be generated automatically at the time that a Demographic form report is created.
Documents and large files are added by first copying the files to the C:\SQLDataStorage\MedicalRecordsSQLDemoData\ directory. The Files can be accessed by hyperlinks or directly from SQL Server fields. The sizes of hyperlinked files are limited only by a computer's hardware capabilities including available storage.
SQL Server varbinary(MAX) fields are limited to a maximum of 2GB per item. Files must have a Windows File Extension that indicates the file type; for example, a period followed by an abbreviation of a file type name like: txt, doc, docx, pdf, jpg, wav or avi. It is necessary to have a reader for one or more of the file types. Examples are Adobe Reader for pdf and Microsoft Word or OpenOffice for txt, doc and docx. Many of the readers are available free of charge or may already be present as part of a system's software. The Microsoft operating system can automatically associate readers with specific file types. Alternately, this database program has VBA code that makes possible user selections of the application programs that are to be used to read specific file types.
EDITING
Editing options are user selectable. The default configuration is that all fields in an existing Form are read only. Clicking on the Edit button results in a pop-up which requests a password. If accepted, the existing Form fields can be edited or a New Form can be created. The password for the Demo program is: "Pass". The editing options can be reset to the default values by clicking the Edit button. The options are automatically reset each time that a new Form is opened.
The details of using the other Forms are in the "Forms" section of "Help".
SECURITY ISSUES
Access provides for password and encryption protection. Unfortunately, there are a number of available programs that can circumvent these protections. Microsoft indicates that the security of Access has been improved. A detailed description is available at:
https://technet.microsoft.com/en-us/library/cc179050.aspx
SQL Server or its equivalent should be used if a higher level of database security is required. Also, the Access security system doesn't provide protection for hyperlinked files that are stored outside of the data tables. It is important to be diligent about data security, but it is also true that no system is absolutely secure. Even if a completely secure encryption system existed, it would not prevent database misuse by a "rogue employee" or a database administrator. Nevertheless, it is wise to use some form of encryption and to avoid connecting a computer that contains highly sensitive data to any network, especially one that has Internet access.
Malware intrusions and misuse of databases have become commonplace in both governmental and private settings. It has recently been reported that malware is even being embedded in the firmware of hard drives and other electronics when they are manufactured. It is extremely difficult to detect this type of malware.
Redspin Corporation monitors data breaches and issues an annual report that summarizes the incidents. The 2014 Report was issued in February 2015. Findings included:
Quote:
· 1,170 Large breaches of Protected health information since 2009.
· 40,862,852 Patient health records affected by breach since 2009.
· 8,899.610 Patient health records breached in 2014 in 164 incidents.
· ~30% of PHI breaches involved a business associate each year from 2009-2014.
End Quote
See:
https://www.redspin.com/resources/whitepapers-datasheets/request-2014-breach-report-protected-health-information-phi-redspin.php
There are current ongoing discussions between congress and a variety of governmental entities regarding legislation that would require "back doors" for all encryption systems that are used in the United States. Many information technology security experts believe that such a requirement would create a major threat to privacy.
See:
http://www.nytimes.com/2015/09/08/us/politics/apple-and-other-tech-companies-tangle-with-us-over-access-to-data.html?emc=edit_th_20150908&nl=todaysheadlines&nlid=49488149
One should be aware that FISA, CALEA, The Patriot Act, HIPAA and Executive Order 12333 (Ronald Reagan 12/4/1981) all contain provisions that give a variety of governmental and private entities access to medical record information with or without a patient's permission.
A link for EO 12333 is:
http://www.archives.gov/federal-register/codification/executive-order/12333.html
Another interesting aspect of medical records is the MIB which is an organization that is supported by a consortium of insurance companies. Dating from 1902, the MIB has maintained an extensive database which contains medical record information along with a variety of personal data. See:
http://www.mib.com/
A "simplified" version of Part 164, the privacy section of HIPAA, is available at:
http://www.hhs.gov/ocr/privacy/hipaa/administrative/combined/hipaa-simplification-201303.pdf
We have used ArticSoft's FileAssurity Open PGP Security to protect all of the files in the MedicalRecords directory. They offer a free 15 day trial of Open PGP and a free PGP decryption program.
See: http://www.articsoft.com/
PGP has a fascinating history. To quote from Wikipedia: Phil Zimmermann created the first version of PGP encryption in 1991. The name, "Pretty Good Privacy" was inspired by the name of a grocery store, "Ralph's Pretty Good Grocery", featured in radio host Garrison Keillor's fictional town, Lake Wobegon." A detailed description is available at:
http://en.wikipedia.org/wiki/Pretty_Good_Privacy
PGP was originally open source code and open versions are still available. Symantec, however, holds the current commercial licensing. They market a number of products based on PGP.
FileAssurity Open PGP Security has been chosen because it is relatively easy to use, has all of the advantages of PGP and is inexpensive. The version we have used is not, however, a command line program that could be incorporated within VBA code to provide one click initiation of encryption and decryption. Commercial versions of command line PGP are extremely expensive.
GPG4Win is one open source possibility, but it has not been fully tested by us at this time. See:
http://www.gpg4win.org/
Microsoft's SQL Server 2014 has a number of features that facilitate data integrity and security. Integrity can be protected with database mirroring and shadowing. There are a variety of encryption/decryption options that help to protect the privacy of databases and the transmission of data between a server and client. The details are described at:
https://msdn.microsoft.com/en-us/library/bb510663.aspx?f=255&MSPPError=-2147217396
Microsoft's SQL Server 2016 has "Always Encrypted" which is described by Microsoft as follows:
Quote
Data security is top of mind, especially for mission critical applications, and SQL Server has been the enterprise database with the fewest security vulnerabilities six years running. To help customers with data security and compliance when using SQL Server on-premises or in the cloud, we are introducing Always Encrypted. Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.
End Quote
See:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx
An excellent introduction is Ron Garcia's Blog (Microsoft) entitled: "Getting Started
with Always Encrypted". It can be found at:
http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx
"Always Encrypted" may be a good approach to user transparent encryption/decryption. It appears at this time, however, that the technology is not compatible with fields that are bound to those in an Access Form.
CHARTING
Microsoft Word Chart is used to graph clinical laboratory test results and other numeric data. Chart automatically creates an Excel table that contains test data. These data are programmatically deleted or replaced and the Excel table is populated with one or more series of the test results that are to be graphed.
Chart is capable of plotting multiple user selectable variables on a single graph.
Graph types include: Bar, Line, Pie, Pivot, Radar, Scatter and Surface.
COSTS
MedicalRecords is Open Source and is supplied with complete source code. It is downloadable free of charge.
All of the currently required file readers are available at no charge.
The Microsoft products Access, Excel and Word are included in Office Professional 2016. There is no additional charge if this software is already available on your computer system. If your version of Office doesnt include Access, you can purchase the add-on for about $100. A runtime version of Access is free. It cannot, however, be used for program development.
An interesting product is Office 365 Home. It includes Word, Excel and Access and can be installed on five different computers. It is lease only and costs $99.99 per year.
Excel and Word are required only if you need to do charting or to print grids. As of now, we have not found an Open Source alternative that works satisfactorily for charting. Commercial charting programs that might substitute for the Excel/Word combination are very expensive.
Microsoft SQL Server Express 2016 is available without charge. It has two limitations.
First, the backup/restore files are not compatible with SQL Server 2016 Standard Edition.
Second, the total size of each database is limited to 10 GB. You can reduce the database size by hyperlinking rather than placing large files in SQL Server.
SQL Server Developer Edition has the same features as Standard Edition except that it is for developer use only. The price of the 2016 version is $38.
See:
http://www.microsoft.com/en-us/server-cloud/products/sql-server/Purchasing.aspx
It is not necessary to install SQL Server on a client system, but it is possible to do this if one wants to test a stand-alone configuration. SQL Server is compatible with Windows 8.1, Windows 10, and Windows Server 2012 R2 or more recent versions of Server.
Finally, you may be fortunate to have access to a SQL Server system that is available at no or minimum charge.
You are welcome to use our SQL Server at no charge for testing purposes. Its URL address is datarx.info.
TRY MedicalRecords
A live version of MedicalRecords can be accessed via Citrixs GoToMyPC. Please contact RERThird@gmail.com if you would like to test the system.
Contacts
Eliot S. Robinson
Cell: 702 330-9921
Internet: eliot_robinson@yahoo.com
Robert E. Robinson
Cell: 850 629-0520
Internet: RERThird@gmail.com
Web and download site: datarx.info (do not use www prefix).
Download Latest Version
MedicalRecordsDemo.zip (6.8 MB)
