Delaware Data Creation Manual

Delaware Data Creation Manual

A Guide for Adapting Clemson University’s Delaware Study Technique to Your Data

Jessica L. Swink

Clemson University

Table of Contents

Introduction 3

Program Description 3

Program Benefits 4

Prerequisite Skills 4

Initial Setup 5

Assessing Available Information 5

Writing Code to Import Tables Automatically 7

Setting up Translation Tables 10

Editing Base Table Queries 10

Editing Intermediate Queries 12

Using the Program 14

Importing Your Data 14

Updating Translate Tables 15

Creating Your Base Tables 15

Creating Your Delaware Study Report 17

Technical Support 18

Dictionary 19

Introduction

When I first started with Clemson University’s Office of Institutional Research, one of the major projects I inherited was the National Study of Instructional Costs and Productivity, commonly referred to as the Delaware Study. As with many of the studies in which Clemson participates, the Delaware Study requires categorization and summarization of data that is not readily obtained from Clemson’s raw data. The information must be run through many steps, including automatic calculations and manual edits, to accurately reflect the information requested by the University of Delaware (UD).

My predecessor left me the MS Access database she used to process the data for this report. Unfortunately, Clemson had recently implemented new human resources software that changed how employee and financial records were coded and stored, rendering the previous process obsolete. Instead of trying to adapt the old database processes, we decided to start from scratch. The result was a version of the semi-automated system available for download on Clemson OIR’s website: http://www.clemson.edu/oir/presentations/delstudy.htm.

Program Description

The Delaware Data Creation System is an MS Access application that uses tables, queries, forms, Visual Basic code, and reports to make reporting Delaware Study information easier. The key to its helpfulness is the form-based graphic user interface that makes it unnecessary for the end user to run every query or even to have knowledge of the mechanics behind every process.

The application also includes an initial setup process that allows you to customize the import process to your own data sources. This is a necessary step if you want to automatically import data into the database. There is no default import process because everyone’s data is set up differently.

The main process menu allows the user to import, categorize, and summarize data into the definitions and formats requested in the Delaware Study. Most of this is automatic with the click of some buttons, although there are a few steps that must be completed manually. The results can be reported either as a paper document similar to the form published by UD for archive or mail transfer or as an MS Excel file formatted to the UD’s requirements for FTP or email transfer to UD.

Program Benefits

Process automation is one of the best ways to increase efficiency in the world of institutional research. The original version of this database tool decreased the time it takes Clemson to report information for the Delaware Study from about 2 months to 2 days or less.

This technique also makes transferring projects to other analysts a lot easier. The new analyst can create the report even before he or she completely understands the full process. Learning the nuances of the data can be gradual instead of occurring in one quick crash course.

The techniques used to create this database tool can easily be used to automate the creation of many annual reports. I would strongly encourage data analysts to explore the behind-the-scenes structure of this program in order to use and even build upon the methods employed here.

Prerequisite Skills

The import setup process requires knowledge of MS Access table and query design and use. Some previous experience with Visual Basic code in MS Access will be helpful, but is not required. Instructions are included for any coding a user might need to do. Once the process is set up, users should only need a basic knowledge of MS Access and the data they will be using.

Initial Setup

When you first open the program, you will see a title screen giving you the choice of {Initial Setup} or {Collect and Summarize Data} (Figure 1). Choose {Initial Setup} if this is your first time using the program or if you need to edit the program setup. In these steps, you are essentially telling the program how to read your data and turn it into meaningful information.

Otherwise, choose {Collect and Summarize Data} and skip to Using the Program in this manual.

Figure 1.

Assessing Available Information

Choosing {Initial Setup} will close the title screen and open the first form of the initial setup process (Figure 2). This screen is designed to familiarize you with the data that will be needed to create your Delaware Study report. Clicking on each button will open one of the base tables or translation tables used in the program in design view. You need to pay special attention to the description of each field and think about the data elements needed to fill them.

Figure 2.

For example, clicking on {Employee Information} will open the base table containing all the information needed about employees in design view (Figure 3). The fields listed in this base table can also be found in the Dictionary segment of this manual.

Figure 3.

Writing Code to Import Tables Automatically

Once you have reviewed the data elements required, click on {Next Step}. The screen that appears next will help you write the code to import your data each year (Figure 4).

Don’t worry if you have never written code in MS Access before. This form is set up to take you through the steps and write the code for you.

Please note that this form only works on MS Access tables, MS Excel worksheets, or text files. If you need to import information from another source, instructions are included later in this manual.

Figure 4.

You need to create a record on this form for each table you want to import. The information requested by the form will allow you to customize the code to fit your data. The box numbers referenced below correspond to the numbers in Figure 4. Do not use double quotes (“) anywhere in these 7 boxes. Use single quotes (‘) if necessary.

Box 1: In box 1, choose the correct data type from the drop down box. Your choices are MS Access, MS Excel, or text.

Box 2: Enter the path where the file is usually stored along with the usual file name in box 2. If the path or filename changes each year, try to establish a standard naming convention for it with only minor changes to designate year. For example, you could have a path and filename like “C:\Personnel Files\Fall 2000\Active Employees Fall 2000.mdb”. In this situation, you should enter the entire path and filename, but substitute “2000” with “yyyy”. The end user will have an opportunity to change the default value you entered here to import the data for the correct year.

Box 3: As part of the import process, an input box will pop up to ask for the path and filename. It will include the default value you entered in box 2, but this is where the end-user can modify the information. This user will probably need instructions on whether or not to change the default and how. Enter one short sentence to instruct them on this in box 3. Using the last example, you should enter a sentence like “Replace yyyy with the year of the data you want to import.”

Box 4: Boxes 4 and 5 are only relevant if the table is in another MS Access database. Ignore these for MS Excel files and text files. This is where you would insert the name of the table you want to import from the database listed in box 2. Try to use as standardized a name for the table as possible. Similar to the example above, replace any changing part of the name with placeholder text.

Box 5: Similar to box 3, input a sentence instructing the end user about any changes that need to be made to the default value in box 4. In both boxes 3 and 5, if no change is needed, still insert a sentence to that effect.

Box 6: In box 6, enter a name for the imported table. This name should always be exactly the same every year or your queries will constantly be breaking. The import process will delete the old table and import or link the new one using this name. The idea is to keep all the tables in the database as generic as possible to avoid having to update queries every year.

Box 7: In box 7, choose either import or link. Importing the table will make a copy of the table and paste it into the database. Linking the table will create a shortcut to the table source. Every time the table is updated at the source, it is also updated in this database. It is recommended to import MS Excel and text tables and link database tables. Linking tables preserves data integrity and saves on storage space.

This concludes the information needed for one table. To add more tables to the import process, you need to add a record for each one. You are actually adding one line in a database table behind the scenes for each imported table. Go to the bottom of the page to the record navigator. Click on the {>*} button to add a new record. Fill in the 7 boxes for each table.

Once you have entered information in the seven boxes for all of the MS Access, MS Excel, and/or text files you want to import, click on {View Code Report}. This takes the information you entered, runs it through a query to write each line of the code, and outputs the results to the report that pops up. This is the code that will import your tables at the click of a button. Before it’s functional you first need to copy the code to the right place in the process. Click on {Go To Import Code} to open the process form and the code behind it. The import code is at the top of the page and looks like this:

Private Sub Import_Data_Files_Click()

'This subroutine will import your data automatically with the click of a button.

'You will only need to fill in the input boxes with the correct data requested.

On Error GoTo Err_Import_Data_Files_Click

DoCmd.SetWarnings False

'Red code from the import code report should be entered

'EXACTLY as written between the two “Set Warnings” commands.

'See report “Code for Importing Data” for an example.

DoCmd.SetWarnings True

Exit_Import_Data_Files_Click:

Exit Sub

Err_Import_Data_Files_Click:

MsgBox Error$

Resume Next

End Sub

Green text in the above code denotes comments that are not part of the actual commands. The only part of the code you need to change is the second (indented) section of green text. Replace this with the red text from the report. You won’t be able to cut and paste directly from the report. This is one limitation of MS Access. You can either type the text into the code EXACTLY as written, or you can export the report to MS Word and copy and paste the text (Menu path = Tools: Office Links: Publish it with MS Word).

After you update the code, go back to the form that opened earlier, Delaware Data Creation. Switch over to form view and click on {Import Data Files}. Make sure everything was imported like you expected. If so, save the Delaware Data Creation form and click on the {Go Back to Initial Setup}. Click {Next Step} on the Initial Setup form. You can also close the code window, but it won’t make a difference either way.

Importing Tables From Other Sources

If you have ODBC access to data warehouse tables that have the information you need, you should manually link to these. Ideally, the names of the tables don’t change from year to year and will not have to be re-linked. Barring any changes to the tables, you should not have to touch these again. You should remove the {Import Tables} button from the main process (in design view) if all of your tables are linked and will not need to be updated.

Setting Up Translation Tables

The next form (Figure 5) allows you to open the 6 translate tables needed and enter data into them. These are the same translate tables viewed on the first initial setup form. If you already have translate tables with the requested information, you may be able to just cut and paste. These tables are formatted and named to fit the information queries expect to see later in the process.

Figure 5.

Explanations for each field can be viewed in design view of the table and also in the Dictionary segment of this manual. When you are finished, click {Next Step}.

Editing Base Table Queries

Just as we cannot predict the format of your imported tables, we cannot predict the relationships between those tables and the structure of your data. The form shown in Figure 6 will open queries in design view that will transfer your data to the four base tables. This step requires that you know how to create queries using formulas and criteria, use inner and outer joins, and understand your data. After this initial setup, the end user will not need to be quite as advanced in using MS Access.

For each query, replace the placeholder table with the table(s) that will feed into the necessary fields. All of the required fields are already created. For every place there is a message such as “Delete this text and …”, you should insert a field or calculation that will result in the required information. You should also add any criteria fields needed to limit the data. If intermediate queries are needed, create those from the database window and link them into the appropriate queries.