Delaware Data Creation System Jessica L Swink Office

Delaware Data Creation System Jessica L. Swink Office of Institutional Research Clemson University

Program Description • MS Access application – Uses tables, queries, forms, Visual Basic code, and reports – Graphic user interface – Semi-automated – Enables the user to import, categorize and summarize data – Customizable

Program Benefits • Increases efficiency – Allows institutions with limited time and resources to take part in this study • Facilitates project transfer to analysts less familiar with the data and processes • Techniques used in this program can be applied to other annual reports

Prerequisite Skills • Design and use of: – Tables – Queries • Inner and outer joins • Formulas • If-then statements • Criteria • Visual Basic coding in MS Access is helpful but not required

Program Use • When you first open the program, you will see a title screen (figure 1) – “Initial Setup” – “Collect and Summarize Data” Manual Ref: page 5 and figure 1

Initial Setup • This step is necessary if: – This is the first time using the program – Data structures or codes have changed since the first setup Manual Ref: page 5

Setup Step 1: Assessing Available Information • Explore the data elements used (figure 2) • Determine the information sources needed to populate the tables used in this program • Base tables – – Employee information (figure 3) Course information Completions/degrees awarded Expenditures • Translate tables Manual Ref: page 5 -6 and figures 2 and 3

Setup Step 2: Writing Code to Import Tables Automatically • Input the information requested for each table or file you want to automatically import (figure 4) • A click of a button will use your information to write the Visual Basic code for you • You copy it to the appropriate place in the code Manual Ref: page 7 -9 and figure 4

Setup Step 3: Setting up Translation Tables • Fill in the codes and descriptions needed for the translation tables: – Department IDs and names – General and Delaware CIP codes associated with departments, majors, and courses – Course method of instruction categories Manual Ref: page 10 and figure 5

Setup Step 4: Editing Base Table Queries • Translates your data into a generic format used by the program. • You will need to: – Link your tables into the query – Fill in formulas and criteria – Follow instructions wherever there is text that begins “Delete this text and…” Manual Ref: page 10 -12 and figure 6

Setup Step 5: Editing Intermediate Queries • Input relating to your data is required to complete these intermediate queries (figure 7) • Again, follow instructions wherever there is text that begins “Delete this text and…” Manual Ref: page 12 -13 and figure 7

Create Your Delaware Study Report • Tables ready for import? • Have there been any changes to the data structure? – If so, go through initial setup again for edits • Have there been any changes to department, major, course, or CIP codes? Manual Ref: page 14

Import Process and Translate Tables • Import data with a click of a button • Update translate tables if any codes have been added or changed Manual Ref: page 14 -15 and figure 8

Create Base Tables • Start at the top left and follow the arrows – Employee table – Course table – Expenditures table – Completions table • Some manual editing required – Resolve duplicates and add missing CIPs Manual Ref: page 15 -16 and figure 9

Create and Use Report • Create the final report with the click of a button • Includes buttons to: – Open the report – Check the university totals – Automatically print the report to the default printer – Export the report to MS Excel fixed column format Manual Ref: page 17 and figure 10

Final Notes • To get the most out of this program: – Standardize your source data structure as much as possible – Delve into the background processes to learn how to customize this program even further

Program Location • http: //www. clemson. edu/oir/presentatio ns/delstudy. htm • Or, go to: • http: //www. clemson. edu/oir – Select “Presentations” – Select “Simplifying the Delaware Study…” • Download the program, manual, and presentation

Contact me for help: • Jessica L. Swink • Office of Institutional Research at Clemson University • Email: pierce@clemson. edu • Phone: (864) 656 -0161