Medicare Cost Report Data: Using Link Specifications in Microsoft Access when Importing Data

Purpose

When importing Medicare cost reports into Microsoft Access, data users should use the “link specifications” option to ensure that each variable imports with the correct data type.

Current Version Date:
12/09/2016

Link Specifications Window

Assigning Link Specifications

Assigning link specifications in Microsoft Access ensures that each variable is correctly labeled and assigned the correct data type.  If users do not define these specifications, the Access table will contain generic column headings such as “Field1”, “Field2”, “Field3”, etc, and may incorrectly assign the data type (i.e. a numeric variable assigned as a character.) When importing Medicare cost report data into Microsoft Access, users will find the link specifications when clicking the Advanced button in the bottom right corner of the Link Text Wizard window.

Data Type Crosswalk between Cost Reports and MS Access

The “HCRIS Table Descriptions and SQL” document included in the “…DOCUMENTATION.zip” file on each facility’s Cost reports website contains a list of the fields, data types, and variable positions for each of the 3 cost report files (Report, Alpha and Numeric). Use the following table to crosswalk the data types found in the “HCRIS Table Descriptions and SQL” document to the corresponding data type in Microsoft Access:

HCRIS Table Descriptions and SQL Data Type Microsoft Access Data Type
CHAR Text
DATE Date/Time
NUMBER Long Integer
Table 1. Data Type Crosswalk between Cost reports and MS Access
Hospital Report File Field Name Field Name Microsoft Access Data Type
RPT_REC_NUM Long Integer
PRVDR_CTRL_TYPE_CD Text
PRVDR_NUM Text
NPI Long Integer
RPT_STUS_CD Text
FY_BGN_DT Date/Time
FY_END_DT Date/Time
PROC_DT Date/Time
INITL_RPT_SW Text
LAST_RPT_SW Text
TRNSMTL_NUM Text
FI_NUM Text
ADR_VNDR_CD Text
FI_CREAT_DT Date/Time
UTIL_CD Text
NPR_DT Date/Time
SPEC_IND Text
FI_RCPT_DT Date/Time
Example 1: Assigning the correct data type to each variable in the Hospital Report File.
Saving Link Specifications

Microsoft Access allows users to save the link specifications entered and then apply the specifications to subsequent files imported. To do so, click ‘Save As’ in the Link Specification window and name the specifications.

Applying Link Specifications

To apply the link specifications to a subsequent file, select the Link Specifications window. Click on ‘Specs’ button to choose the correct specifications. Select the correct file, then click ‘Open’.