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
Save as PDF (print)

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’.

Save as PDF (print)

Article Information

Author:
Faith Asper, MHS, CIPP/G
Level:
Intermediate
Topic:
Analytic Guidance
Program:
Medicare
Category:
Plan and Provider
Disclaimer:
The process and materials mentioned as part of this KnowledgeBase article are current, as of the publication date on the article, to the best of our knowledge. The examples provided are correct in the aggregate but may not apply to every subgroup or circumstance that a researcher may wish to study. It is up to the researcher to conduct analysis and confirm that the patterns described in this KnowledgeBase article apply to his/her particular study. If your research findings appear to contradict the advice provided, please contact ResDAC at resdac@umn.edu.