Navigating the Schema

This section assumes that you have a working knowledge of MS Access.  Navigate the Schema using the Query engine in Access or your own custom code.  To get a quick start on browsing the schema tables we strongly recommend that you try using the Schema Browser.  It allows you to generate queries and view the resulting SQL statements. This will be of great help for you in determining how to write your own queries or views to retrieve your HL7 data.

The most common navigation need is to retrieve HL7 message data that has been imported into the system.  To do this there are 2 tables which will be of extreme importance to you.

Table definitions for the Schema Master table and the Message Manifest table

Segment Data Tables

Another important thing you'll need to know before you start retrieving HL7 data elements is how to determine which Access Table contains the data element(s) you're looking for.  All of the segment data tables will be dynamically named based on three things, the Schema Prefix, the HL7 Segment Name and the SIZE of HL7 data fields that segment contains.  HL7 Message segments are made up of multiple HL7 data fields. Each HL7 data field is a specific HL7 Data Type and contains 1 or more (usually more) components. Each COMPONENT will represent 1 database field in an Access Table.

If the HL7 segment contains 40 or more HL7 data fields (like the GT1 [Guarantor Data] or IN1 [Insurance Information] segments), the segment will be represented by two tables.

Example: If the Schema Prefix is V23 the table for the PID (Patient Information) will be named V23_Segment_PID_A.  The IN1 (Insurance Information) segment tables would be named V23_Segment_IN1_A which would hold data for fields 1-39 and V23_Segment_IN1_B which would hold data for fields 40-nnn.

The SQL Statement:

Select * from v23_Segment_PID_A Where MessageID = '9A3FF083DE1F384584553969F1745497' Order By IDX 

Would return 1 row for each PID segment contained in the message.

Segment Field Names

Of equal importance is how to identify which data fields you care about within each table.  Here is where some knowledge of HL7 structure is required.  HL7 Message segments are made up of multiple HL7 data fields. Each HL7 data field is a specific HL7 Data Type and contains 1 or more (usually more) components. Each COMPONENT will represent 1 database field in an Access Table. ALL Segment data fields will be defined either as Text or Memo Access data types. When you use the schema engine to create the tables (see Creating Tables) the program will analyze the HL7 Vendor Definition used in the Schema to determine how many tables to create and what they should be named.

A common question we receive is "Why are there so many Access Tables created?". The answer is really pretty simple. MS Access has a built-in restriction when installed which limits the number and size of data fields you can place into a single table (Max Size is 2KB excluding Memo fields). An HL7 Segment may be made up of 80 HL7 fields which in turn have 7 or 8 components each, etc etc. Take for instance the Patient's Last Name (the PID Segment Field #5) which is usually an HL7 data type XPN (Extended Person's Name) which usually contains 7 components. The Schema engine creates 7 Access database fields of 40 characters each to hold data. This means that for every HL7 data field of type XPN the engine must create 7 access fields for a total size of 280 characters. Using this logic if an HL7 Segment consisted of only TEN HL7 data fields and each one was of a type XPN the engine would have to create TWO Access tables to hold the data.

As an example we'll use a common field that everyone can relate to: The patient's last name.  Assuming a Schema Prefix of v23, to access the patient's last name first you need to know which segment in the message contains it (the PID segment).  This gives you the table name V23_Segment_PID_A.  To know which field contains the patient's last name you have know which HL7 data field contains the patient name and which specific COMPONENT within that field contains the last name (Field 5 Component 1).  Field names within the tables will be derived as <segment>_F<fieldnumber>_C<componentnumber> so the field name for the patient last name is: PID_F5_C1

The SQL Statetment:

SELECT IDX, PID_F5_C1 As LastName, PID_F5_C2 As FirstName

FROM v23_segment_pid_a v

WHERE MessageID = '053B06284340B64C8EF6D79BD2AB3817'

Order By IDX

Would return 1 row containing the patient's last name for each PID segment contained in the message.

.

Sample Segment Report and Data Type for PID (Patient Last Name)