Unleashing the Power of Microsoft Access for IR Reporting

1
Unleashing the Power of Microsoft Access for IR
Reporting
Richard Backes, Senior Associate Registrar,
Washington State University Cathy
Fulkerson, Assistant Director, Institutional
Research, Washington State University James
Roche, Director, Institutional Research,
Washington State University

• April 20, 2006 / AACRAO 2006 Annual Meeting
• Session ID 232

2
Overview

• Richard Reporting on the Post-secondary
  Centralized Higher Education Enrollment Reporting
  System (PCHEES) used by the state of Washington
• Cathy Automating common and regular database
  manipulations for importing, transforming, and
  exporting data
• Jim Integrating Access with other data and
  programs, such as Excel, SPSS, batch files, and
  Windows task scheduler

James Roche
3
Overview

• The Access-specific items well touch upon today

James Roche
4
Enrollment ReportingUsing MS ACCESS

• Post-secondary Centralized Higher Education
  Enrollment Reporting System (PCHEES)

Richard Backes
5
History

• Mainframe Generated reports
• Hand-entered into Lotus 1-2-3 spreadsheet
• Macro-created reports

Richard Backes
6
History

• Drawbacks
• Reports were static
• Changes to information were done manually
• Changes to data not reflected in reports, and
  vice-versa

Richard Backes
7
Change to Unit Record

• State of Washington went to unit record
  collection
• Access database with Visual Basic program
• Crystal Reports
• Encrypted database sent electronically to state

Richard Backes
8
PCHEES System

• Files imported from mainframe
• Student file
• Registration file
• Course file
• Site file
• Creates Access database with 27 tables

Richard Backes
9
PCHEES System

• Data scrubbing
• Corrections
• Post census day changes in reporting
• Complete enrollment FTE review
• Check reports for accuracy
• Cross-reference tables

Richard Backes
10
PCHEES System

• University enrollment reports
• Generated directly from PCHEES report
• Consistent with state reporting
• Consistency among various reports
• Data sent to state using PCHEES

Richard Backes
11
PCHEES System

• Benefits
• Data driven
• Retain database of census data for other
  reporting
• Underlying data can be analyzed when verifying
  accuracy of reports
• Variety of reports

Richard Backes
12
PCHEES Reports

• Reports include
• Summary (FTE and headcount)
• Students by age, sex, and class standing
• (on campus, off campus, on/off campus)
• FTE reports (on, off, on/off campus)
• State-funded enrollment by site
• Entering students by class standing and source

Richard Backes
13
PCHEES Reports

• Reports include
• Students by geographic origin
• Washington/out of state transfer students by
  class standing and source
• Continuing student by class standing
• Students by credit-hour load
• Students by residency status
• FTEs by IPEDS-CIP discipline/course Level
• Students by major area of study

Richard Backes
14
PCHEES System

• Visual Basic front end
• Importwindow
• Four text files for input

Richard Backes
15
PCHEES Tables

• Main four are- Students- Registrations-
  Courses- Sites
• Most othersare codetables

Richard Backes
16
Student table
Richard Backes
17
Registration table
Richard Backes
18
Course table
Richard Backes
19
Site Table
Richard Backes
20
PCHEES Reports

• Select reports
• View orprint reports
• Example reportsfollow

Richard Backes
21
PCHEES Output
Richard Backes
22
PCHEES Output
Richard Backes
23
PCHEES Output
Richard Backes
24
PCHEES Output
Richard Backes
25
PCHEES Output
Richard Backes
26
Overview

• PCHEES demonstration

Richard Backes
27
PCHEES From State Reportingto Semi-Automated
Management Tool

• Deliver and import additional data and
  standardized queries
• Transform current data
• Create new database tables
• Export final data to other offices and for other
  purposes

Cathy Fulkerson
28
Automate Data Delivery to Access

• ODBC data delivery
• Establish an ODBC reference to Access database
  for data delivery
• Deliver same warehouse data fields with current
  data as an Access table every semester
• Import mainframe download
• Mainframe text file imported using same file
  specifications each semester

Cathy Fulkerson
29
Transform Current Data

• Macros and modules within Access split fields
  apart, perform calculations, create new fields
  and tables.
• Drop-down menus in Access macros facilitate the
  process
• What used to be run manually as a series of
  queries can now be automated

Cathy Fulkerson
30
Create New Data

• Course-load data facilitates tuition revenue
  modeling
• AAFTE, combined with faculty and grad assistant
  FTE and direct instructional expenditures,
  support annual budget hearings
• Track high-demand enrollment

Cathy Fulkerson
31
Deliver Final Data

• Macros export data for use elsewhere
• Shared drive with the Budget and Provosts
  Offices
• Secured IR web site for college-level data
  delivery

Cathy Fulkerson
32
Access on Steroids Some Examples

• Integrate and analyze data from different sources
  

WSU
IPEDS
WA K-12
Access database
NCES
WA OFM
EPS
Census
BLS / BEA
James Roche
33
IPEDS Streamlined!

• Before Access, analyzing IPEDS data was tedious
  and time consuming
• Access streamlines and broadens our scope of
  analysis with easily imported IPEDS data, which
  can often be used with existing queries

James Roche
34
IPEDS Streamlined!
James Roche
35
IPEDS Streamlined!
James Roche
36
Using Access to Populate Excel

• Access allows us to easily import data into Excel
  for the Institutional Research admissions
  dashboard

James Roche
37
Advanced Analysis

• Once data are in a database, linking them to
  other analytical software, such as SPSS, is a
  simple task.

James Roche
38
Pushmi-Pullyu
4
Task Use FTP to transfer ENX ASCII file Task
Run Access and start Monday module Access
Establish download date Access Use File Specs to
import ENX file Access Transfer previous years
data into database Access Combine previous
years data with this years Access Calculate
levels and update table Access Create table for
web data Access Create special tables/queries
for specific users
Scheduled Tasks program calls Access macro and
module to Push (via e-mail) tables to end users
as Excel files
4
4
4
4
4
Web pages are Pulled on demand by end user
4
4
4
James Roche
39
Work While You Sleep

• Windows Scheduled Tasks program provides great
  flexibility for automating your programs

James Roche
40
Sleep While You Work

• Use Windows batch files to do the work

James Roche
41
For More Information

• Richard Backes backes_at_wsu.edu
• Cathy Fulkerson fulkerso_at_wsu.edu
• James Roche jroche_at_wsu.edu
• Presentation available online at
• www.ir.wsu.edu
• (Click on Presentations)

James Roche