The Background
Upon informing my principal that I was ready to return to the classroom after a yearlong sabbatical, I received a kindly worded email with the offer of a lifetime: I was being asked to become [drumroll, please…] the co-sponsor of my school’s chapter of the National Honor Society!
[Insert angelic fanfare here.]
Who wouldn’t want to be responsible for dealing with hundreds of applications, planning an induction ceremony, and overseeing countless hours of service projects?
In all seriousness, it was an opportunity to be more involved in the school community, and I knew I would get to meet and work with some awesome kids. So I accepted the offer.
As it turned out, my assessment was mostly correct, but I also discovered that the application process was just as annoying as I had anticipated.
The Problem
It didn’t take long to discover that I had inherited an administrative nightmare.
During a brief onboarding session with the outgoing sponsor, I learned that each application consisted of a packet containing all of the following:
- A detailed overview of the selection process
- Guidelines for completing the application
- Spaces to fill in basic information like name and grade
- A two-page form for documenting community service, involvement in extracurriculars, leadership roles, and work experience along with spaces for adult supervisors to sign off on each activity
- A final signature sheet for students and parents to vouch for the accuracy of the information provided
- Typed, separately attached responses to several open-ended questions
- Various ancillary, separately attached materials that some students chose to include
As I’m sure you can imagine, this added up to quite a few pages per packet. Now, recall that literally hundreds of students apply for membership in my school’s NHS chapter each year. This amounted to a towering stack of paperwork.
My co-sponsor and I had to go through each application, one-by-one, to build a list of applicants. We then had to distribute the packets to five committee members, each of whom would rate each applicant on a handful of criteria. When all the committee members had submitted their ratings, we needed to compile them all together and add them to the master list. Then, after holding a meeting with the committee to make final decisions, we needed to deliver hundreds of letters to let individual applicants know whether they were accepted.
We were able to somewhat streamline this by dividing the applications into five separate stacks so that the committee members could all work on their ratings at the same time. But coordinating this process quickly got out of hand. Because the time it took the committee members to evaluate applications varied rather widely, evaluators who were ready to move on would be forced to wait while the applications they hadn’t seen yet languished on someone else’s desk.
By Year 2, we figured out that we could further improve the process by using a shared Google Sheet for each evaluator. This made it easier for committee members to submit their ratings and somewhat reduced the time it took my co-sponsor and I to compile the master list. But data entry was still a huge burden and still subject to inaccuracy. This solution also did nothing for the stack-availability problem.
The Objective
After two years of this, it was time for a change. I set out to overhaul the entire application process with the following goals in mind:
- Eliminate most—if not all—basic data entry obligations by shifting the burden onto the applicants themselves. (This would have the secondary effect of reducing the occurrence of misspelled names on membership certificates.)
- Reduce waiting time by enabling committee members to simultaneously evaluate all applications.
- Eliminate the need to manually compile ratings into a master document.
- Maintain the “privacy” of each evaluator’s ratings until the decision-making meeting. (To keep the evaluation process as fair as possible, we didn’t want committee members to be influenced by their colleagues’ ratings.)
- Reduce the number of incomplete applications that we would receive each year. (These were sometimes due to misunderstandings of the instructions and frequently resulted in frustrating conversations with non-accepted students and parents. One of the most common mistakes was a failure to obtain all of the necessary signatures.)
- Make it easier to communicate with students, especially by email. (I had already figured out how to use mail merges to generate and sort the notification letters, but the routine was rather kludgy. Meanwhile, there were always some students whom we had trouble finding when it came to physically delivering letters.)
- Maintain the ability to monitor committee members’ progress so that reminders/encouragement could be provided as needed. (While the existing system of five stacks was a pain, we could at least track how many stacks each member had completed.)
- Improve our record keeping and research abilities by consolidating information about the entire process (invitations, applications, ratings/decisions, meeting notes, selections, etc.) into a single location.
- Reduce the necessity of paper archives.
The Solution
What I ultimately created was a “semi-electronic” application system that uses a website, an online form, and a database to significantly reduce our reliance on paper, automate much of our student communication, and eliminate the inefficiencies in the evaluation process.
Part 1: The Application
The first step was to digitize as much as possible of that multi-page behemoth of an application packet.
For starters, I moved all of the application instructions (including the process overview and guidelines) to a very simple website that I created using WordPress. (I also purchased an appropriate domain name that would make it easy to find.) The link to this website was communicated to eligible students via email and printed letters.
The website allowed all the instructions to live in one place, free from the distraction of the application itself. (My hypothesis was that the packet made it easy for students to skip straight to the application without first learning all the details.)
Next, I broke up the application itself into two parts: an online form and a paper-based documentation sheet.
The online piece was built using Google Forms and asks each applicant for the student’s name, ID number, and personal email address. It also provides space (on a second page) to answer the open ended questions.
Students must log into Google using their district-provided accounts to access the form, which means we are also able to collect their official school email addresses as part of the process. The electronic application places the burden of spelling names correctly on the applicants themselves while also ensuring that we have access to at least one email address (either personal or school-provided) that each student is likely to check.
The second part of the application is a paper form that students can download from the website. The form consists of a single, one-sided page with space for the applicant’s name and ID number, an area right up top for the oft-forgotten student/parent signatures, and a condensed version of the service/activity chart that used to be part of the packet.
In order to keep the form to one printed page, I eliminated the boxes for work experience and instead provided directions encouraging students to write in this information on the back of the page. (This section has always been optional.) Some students would still attach additional materials to their application, but my suspicion (which proved accurate) was that many would simply submit the single page.
By keeping this part of the process on paper, I was able to maintain the requirement that students verify the accuracy of their application via signatures from activity supervisors.
Part 2: The Database
Now that we had a better system for students to learn about and complete their applications, we needed a way to process everything.
Google Forms allows you to page through submissions one at a time. This, however, would require giving the entire committee access to the form’s back end, which was not ideal. Google Forms can also output all of the submissions as a Google spreadsheet. This is more convenient for data processing, but a spreadsheet is a terrible format for reading essays and thinking about individual applicants.
More importantly, neither format would address the fact that a vital component of the application was still paper-based, meaning we would still run into the “stack availability” problem.
What we needed was a way to stitch together the two pieces of the application and present all of the information to the committee members in a user-friendly manner. Bonus points if they could all access the data simultaneously and use the very same system to submit their ratings. And even more bonus points if my co-sponsor and I could use the same system to view all the ratings and complete all of the other administrative tasks.
What we really needed was a multi-user relational database with a graphical front end—something like Microsoft Access.
Access is the odd man out when it comes to the Microsoft Office suite. Nearly everyone of a certain age has used Microsoft Word (except for many of my students, who have grown up using Google Docs as their primary word processor), and plenty of folks have looked at an Excel spreadsheet even if they don’t know how to create one. Businesses across the world still rely on Outlook for email, and PowerPoint presentations are so ubiquitous that the name has become virtually synonymous with “slideshow” (to say nothing of the “Death by PowerPoint” syndrome).
Access is a whole different animal. Only included in the “professional” versions of Office, Microsoft’s consumer-focused database application is as inscrutable as it is powerful. It’s the wonkiest piece of the puzzle, requiring a far more specialized knowledge set to operate than the rest of the office suite—and I hate it.
In my opinion, Access is buggy, clunky, and terribly difficult to learn. My favorite database application, if one can have such a thing, is FileMaker Pro, but it’s a niche product and dreadfully expensive if you want to use it in a multi-user capacity. It was entirely out of the question for this project; meanwhile, Access is pre-installed on every teacher’s computer in the district.
What I ultimately created was a three-part database solution. The first part was a back-end database without any user interface, just a collection of data tables. I was able to copy and paste the Google Form data into one table. I used another table to paste in relevant student data (such as where to find them during certain periods) provided by our Student Services office. Yet another table held the list of eligible students (also pasted in from a spreadsheet provided by Student Services). This back-end database would also be used to store the committee member’s evaluations.
For the front end, I created two more Access files, one for the sponsors and one for the committee members. These files don’t actually store any data. Instead, they connect to the back end database file, which I placed on a shared server that all teachers have access to. But what the files do provide is the user interface.
Committee members have a limited interface consisting of two different views. One provides an overview of all of the applicants and the committee member’s progress. This list can be sorted alphabetically or by status. Clicking on a name opens up the second view, which is a carefully laid out interface displaying that student’s basic information and essay responses. This information is all read-only.
The interface also includes a set of controls for selecting a rating for each criterion as well as forward and back buttons to page through each student on the list.
But what about the paper-based activity sheets? This is where things get a little crazy. My co-sponsor and I used a high-end copy machine with a document feeder to scan all the applications to PDF. We then went through all the PDFs and changed each file name to be the applicant’s student ID number. You could argue that this replaced one data-entry fiasco with another, but the truth is that this process went a lot more quickly than the original, which involved leafing through documents and typing every student’s name and grade into a spreadsheet.
I placed all of those PDFs on the shared server. I also added a button to the database interface that executes a script (written in Visual Basic) that finds the appropriately-named PDF on the server and displays it to the evaluator. And thus we achieved the Holy Grail; each committee member could now have simultaneous access to every student’s entire application.
I emailed a separate copy of the front end Access file to each committee member. However, I pre-programmed each front end file with a different user ID. This allowed each member to create a separate set of ratings that only that member could see. I also found some techniques, after some relentless Google searching, that allowed me to lock down the file so that my users only had access to the graphical interface and could not possibly find any of the underlying data queries or code. (I needed to make this rather fool-proof since most of my colleagues have never really used Access before.)
As I mentioned above, I made a second front-end interface file that was just for the sponsors. This file allowed us to see all of the ratings and also record the committee’s final decisions. (We projected the interface onto a whiteboard during the meeting so all could see.)
The sponsors’ front end included numerous other features, including the ability to automatically generate notification letters (sorted by student location), automatically send notification emails (to both personal and official addresses), produce a spreadsheet of accepted (or rejected) students for chapter administration, and monitor the progress of each committee member. I even included some functions to track down incomplete/mismatched applications. There were a handful of other reports and functions, but those are some of the most relevant.
The Outcome
Developing and refining the database took a lot longer than I anticipated (easily over 50 hours), but I was able to do most of this work over a couple weeks of Summer Break. It was totally worth it, though, because I now understand Microsoft Access so much better than before. I still hate it, but I can now confidently say that I’d be willing and able to use it for future projects.
More importantly, the committee members loved the tool. They found it easy to use and really appreciated the fact that they didn’t have to wait around for their colleagues to finish their evaluations. The sponsor interface provided a quick and easy way to cycle through each application during the decision meeting. And although scanning and naming the PDFs took a while, we spent considerably less time entering data, copying and pasting records from one location to another, and having to go back and fix mistakes. (We did inevitably mistype some of the ID numbers when naming PDFs, but the mismatched application finder made it very easy to find and fix these.)
Plus, now that we have those PDFs, we can keep a digital archive of the old applications in case we ever have to refer back to them. (Previously, we had to keep the massive stack of paper applications in an overflowing file cabinet.)
My hypothesis that separating the application from the instructions would result in fewer incomplete applications also seemed to play out. In fact, far more students appeared to notice certain minor aspects the application (such as the option to provide letters of recommendation) than ever before. I have concluded this because of the increased number of students who included recommendation letters as well as the surprising number of emails I received with questions about recommendations. (None of the language in the instructions had changed, so it really did seem like it was about the format. But because of all the questions, we know that we do need to change some of the language next year!)
The most exciting thing about all this is that I won’t have to do any database development work next year. We should be able to create new copies of all the files, paste in all the data, scan in all the PDFs, and go through the same process.
Sponsoring the National Honor Society still has plenty of other complications, but my co-sponsor and I were relieved to have removed our biggest annual administrative headache.