HEI Data Submissions: Correcting Data Already Loaded to the Database

Revised September 25, 2012

There are two types of corrections that data reporters may need to make after locating errors in their files: the first occurs to data files prior to loading onto the database; the second applies to changes after the data files have been loaded onto the database. This document deals with the second type of correction.

There are three options for updating records on the database, depending upon the file submission. The update feature was created so that schools could change data fields on records that exist on the database without having to remove records from other file submissions that depend upon that data. The update feature varies slightly depending upon the file because of the primary keys on each file. The chart below presents all the files grouped by the steps needed to update the records for each file. Also listed are each file's primary key identifiers.

In order for a delete or update to function properly, the record being deleted or updated must match the row in the database on the key identifiers.

 

Update Type  1

File or Process File Name Key Identifiers Delete Update

AI

Area Inventory

Campus, Physical Structure Identifier, Addition Identifier, Area Identifier

To remove rows from the database set the delete switch for a given record to “Y”. When the file is loaded to the database, that row will be removed. To update records already existing in the database, set the delete switch to “N” and make sure that the record with the new value matches the key identifiers of the row on the database being updated. The values from the latest record will replace the values from the previous record.

AP

Academic Programs

Program Code, Campus, Year, Term

CI

Course Inventory

Course Identifier, Institution, Year, Term

CT

Cohort Tracking

Student Identifier, Institution

DL

Distance Learning Courses and Sections

Course Id, Institution, Year, Term, Subject Code

PS

Physical Structure Inventory

Campus, Physical Structure Identifier, Addition Identifier

SE

Student Entrance

Student Identifier, Institution Assigned Switch, Admission Area, Institution. For changes to occur the file must be submitted in the Year and Term of the original submisison. This may require a window to be reopened.

 

Update Type  2

File or Process File Name Key Identifiers Delete Update

AM

All Employee

Employee Identifier, Campus, Year

To remove rows from the database set the delete switch for a given record to “Y”. When the file is loaded to the database, that row will be removed. To update records already existing in the database, two records will be needed within the same file submission. The first record, with the Delete Switch set to “Y” needs to match on all fields the row on the database being updated. The second record will contain the new information with the Delete Switch set to “N”.  Always list all records to be deleted before the records to be inserted.

BD

Budget Data

Record Type, Budget Type, Fund

CB

Changes in Funds Balances

All fields

CN

Course Enrollment

All fields

CP

College Expenditure

Campus, Year, College ID

CX

Campus Expenditure

Campus, Year

DC

Degree/Certificate Earned

All fields

EE

Education and General Funds Expenditure

Campus Code, Restricted/Unrestricted, Expenditure Category, Expenditure Description

FD

Faculty and Instructional Non-faculty Demographics

Faculty Identifier, Main Campus or Institution Code, Year

FX

Funding Unit Expenditure

Funding Unit ID, Year, Campus

IO

Instruction and General Expenditures by Object

Institution, Year, Restricted/Unrestricted

OC

Off-campus Course Sections

All fields

RF

Revised First Enrollment

Student Identifier

SN

Student Enrollment

Student Identifier, Institution, Campus, Year (of submission), Term (of submission)

SR

Sources of Education and General Revenues

Campus, Year, Unrestricted/Restricted

TF

Undergraduate Tuition and Fees

Student Identifier, Institution, Year, Term

UF

Unit Record Financial Aid

Student Identifier, Institution, Year, Term

 

Update Type 3

File or Process

File Name

Key Identifiers

Delete Update

AC

Alternative Credit

All fields

To remove rows from the database set the delete switch for a given record to “Y”. When the file is loaded to the database, that row will be removed. To update records already existing in the database, two files will be submitted.  First, remove rows from the database by setting the delete switch for a given record to “Y”. When the file is loaded to the database, that record will be removed.  Add the updated records to the database by submitting a second file with the new information and the delete switch set to “N”.

AL

Funds Assets and Liabilities

Fund field, Institution, Year

AS

Academic Scholarship Certification

Student Identifier, Term, Year

CF

Changes in Current Funds

All fields

CS

Course Section Schedule

Campus, Structure Identifier, Addition Identifier, Area Identifier, Course Identifier, Course Section Identifier

CU

Credit Update

 

FF

Faculty and Instructional Non-faculty Funding

Faculty Identifier, Campus Code, Fiscal Year, Funding Unit

NF

Name File

 All fields

NT

Nurse Education Assistance Loan Program (NEALP) Certification File

Student ID, Year, Term, Institution

NV

Nurse Education Assistance Loan Program (NEALP) Verification File

Student ID, Year, Institution

OG

OCOG Grant Certification

Student Identifier, Institution, Year, Term

OR

Other Use of Instructional Rooms

Campus, Year (of submission), Structure Identifier, Addition Identifier, Area Identifier, Activity

OS

Other Source of Funds

Campus, Structure Identifier, Addition Identifier, Other Sources of Funds, Year

SP

Special Populations

All fields

ST

Course Sections Taught

Campus, Year, Term, Course, Section, Faculty Identifier

TC

Tech Prep Consortium Tracking

All fields

TM

Transfer Module

All fields

TR

Trio Tracking

Student ID, TRIO code

WO

War Orphans Certification

Student ID, Institution, Year, Term

XR

Cross Registration

All fields

 

Special Cases

File or Process

File Name

Key Identifiers

CU

Credit Update

The credit update file is an update in and of itself. Data loaded from this file cannot be erased from the database, but a new CU file matching on all seven key identifiers (Campus, Year, Term, Student ID, Course ID, Course Section ID, and Credit Hours) can be submitted, and upon loading, will overwrite the previous data.

DC: Resubmitting Files Prior to 2003

Degree/Certificate Earned

In 2003 we began collecting Academic Program Code, which added fifteen more characters to the DC file. The DC file edits now require this field for any year, including those prior to 2003 when this field was not collected. When correcting DC files for terms prior to 2003 please insert "PRECOLLECTION" into this field. The edits have been created to accept the phrase "PRECOLLECTION" for years prior to 2003.

IC (from SE): Correcting Admission Area and Institution Assigned Switch

Identifier Change

Please note that all fields in the SE file may be updated except for the Admission Area and the Institution Assigned Switch. The Admission Area in the SE table is part of the key, since multiple admission areas may be entered about a student at a school. In order to correct the Admission Area, all files that reference that student identifier for that admission area need to be removed.

In order to correct the Institution Assigned Switch use the Identifier Change (IC) file. This can be accomplished by submitting two IC files. The first IC file will contain the Student Identifier with the bad Institution Assigned Switch as the "Previous Identifier." The "New Identifier" in this record will be a dummy identifier. Once that file is loaded submit a second IC file. This file will contain the dummy identifier as the "Previous Identifier" and the correct Student Identifier and the correct Institution Assigned Switch as the "New Identifier."

SE, AM, FD: Resubmitting Files Prior to 2009

 

In SM 2009 we began collecting race/ethnicity codes in the SE, AM and FD files, and high school codes and graduation year in the SE file. Please be sure to use the appropriate file layout for the year/term which you are trying to correct.

SN: Resubmitting Files Prior to Autumn 2000

Student Enrollment

In autumn 2000 we began collecting Cumulative Credit Hours Earned, which added five more characters to the SN file. The SN file edits now require this field for any term, including those prior to autumn 2000 when this field was not collected. When correcting SN files for terms prior to autumn 2000 please insert "00000" or the real Cumulative Credit Hours Earned into this field. The edits have been created to accept the zeros for terms prior to autumn 2000.