National Association of Field
Training Officers
Using Microsoft Access
to Track and Catalog Performance
This intention of this
article is to give you a foothold into the world of Access programming. This may sound a bit intimidating if you have
never “programmed” anything before.
Trust me… it is not hard. It just
takes a little time and patience. Unfortunately,
this one article will not give you everything you need. There are books 1,200 pages long written on
the subject that just scratch the surface.
I will give you a quick taste of what is possible and what we have been
developing at the Indiana University Police Department.
One of the strengths of any good Field Training Program depends on the amount and quality of documentation. The documentation is going to back up any decision the Field Training Unit decides to make down the road regarding the Probationary Officer. If termination is required, you had better hope your ducks are in a row. If the documentation is not there, you are putting yourself and the department in a precarious situation that will likely end in a lawsuit.
Microsoft Access provides an effective solution to track and catalog the performance of Probationary Officers. The time needed to fill out the necessary forms is typically cut in half. This obvious benefit to this is BETTER documentation. The Field Training Officer can take more time concentrating on the CONTENT as opposed to the technicalities of filling out the form. Microsoft Access also enables you to compare data among the Probationary Officers and Field Training Officers. Generating reports on training data is usually a simple 2-step process; push this button, push that button.
The following is a list of several of the Pros and Cons of using Microsoft Access in relation to traditional “paper work”:
Pros
Enables the Field Training Unit to keep all training data in a centralized location (no need for a ton of binders and folders that get lost or go flying of the hood of your squad on your way to a hot call)
Reports are generated at run-time (as soon as data changes or is entered, the reports will reflect the changes)
Microsoft Access uses a similar interface to other Microsoft programs
You can easily
compare
Eliminates the need for paperwork
Easily email forms and reports to the command staff as needed
View any information in a graphical format
Amount of time spent completing reports is dramatically reduced
Cons
Money – Microsoft Access can cost as much as $339.00 per computer (That is if you do not already have it…many people do not realize that they actually already have it installed)
Time – it takes a bit of dedication to the project to bring it to life all the way from the design stage
The decision to use Microsoft Access as a compliment to your program is not a difficult one. You either need someone who knows how to program in Access, someone with enough time to sit down and work through it, or money (more on this later).
About Microsoft Access
Microsoft
Access is the database management utility within the Microsoft Office Suite
(Outlook, Word, Excel, and PowerPoint). What
is a database? A database is an
organized collection of specific information for a specific purpose. Microsoft Access allows you to organize and
manage all the information in the database.
The database being all the information collected in the Field Training
Program.
Imagine an Excel spreadsheet. Most of us are familiar with Excel spreadsheets. A spreadsheet is a collection of related information organized into rows and columns. Now imagine four spreadsheets with inter-related data on each sheet. Put those four spreadsheets together and throw in a few forms and reports and you have a database – almost.
I say “almost” because Access has more parts and functions to it than just tables, forms, and reports. An Access database can have as much or as little functionality as the programmer desires. That is why Access is an excellent program for beginning developers as well as experienced designers.
Microsoft Access has all the bells and whistles. It takes a little know-how to use the bells and whistles but you do not have to use them to have an excellent database. The programming language used in Microsoft Access is Visual Basic. However, you do not even have to use Visual Basic. That is the key. It is easy on the beginning developer. There are many “wizards” and tools that help you create the necessary forms, tables, and other items without having to do any actual programming.
An Access database is divided up into several parts:
Tables – similar to the spreadsheet in Excel, holds the information
Queries – allows you pull specific information from the tables
Forms – this is the user interface for interacting with the information in the tables
Reports – for extracting and viewing the information in the tables
Pages – for extracting and viewing the information in the tables in a browser (IE)
Macros – small snippets of code
Modules – large snippets of code
Microsoft Access operates in very much the same way as Microsoft Word. All the common menus and interfaces are there. If you can use Microsoft Word, you can use Microsoft Access.
Designing the Database
The first step in designing a database is to sit down with a piece of paper and a pen. It is always easier to conceptualize the database on paper first and then begin creating it in Access. Here are a few questions you should ask yourself:
What do you want the database to do?
What information do you need to collect?
What forms are to be entered into the database?
How many people will have access to the database?
What kind of security do you need?
How will you access the database (MDT, computer workstation, PDA)?
How many tables do you need?
What relationships will the tables have to each other?
How many forms will you need?
Once you have answered these questions it is time to start getting your hands dirty in Access. It is often easiest to start by looking at all of your program forms and figuring out what information needs to go into Access. Then determine how the information from each form will interact with each other.
By examining your program forms and the information requirements of each you can start constructing the tables. Once you have designed the tables, it is quite easy to put together the necessary Access forms and queries. Just follow the road map you created for yourself and it will all work out. You may hit a couple of obstructions along the way but working through them will make you a stronger developer.
If it seems like I have oversimplified things, you are right. I have. The process of designing the database takes a while. Patience and a sense of humor are pre-requisites.
Implementation
After you have completed the database, it is time to get it on a computer where members of the Field Training Unit will have access to it. Let the members play with it and solicit as many suggestions as you possibly can from them. This can be somewhat frustrating but it is extremely valuable. Frustrating… because you may not be able to immediately figure out how to do what they want it to do (have the program evaluate the recruit and complete the DOR by itself – still haven’t figured that one out). Let the people that are going use it suggest changes and fixes.
This may take quite some time depending on where you want to go with it. The Field Training Unit of the Indiana University Police Department conducted a multiple month trial. The program was made available for the FTOs to play around with it and then we ran four mock recruits through it. All scores were made up but it acquainted the FTOs with the program and we were able to see how it stood up to the pressure.
You should finalize how you are going to use the program. Are you using MDTs? PDAs? Over a network? Depending on how you answer these questions depends on the final touches you put on it.
A few of the options are as follows:
Put the database on a network server and access it over Mobile Data Terminals (MDTs) or Personal Data Assistants (PDAs)
Put the database on a network server and access it over workstations
Put half the database on the network and the other half on MDTs or workstations
Put the database on a computer and work solely off that one.
These are just a few of the options. The final decisions you make will be dependent upon the setup. If you can imagine a setup, it is probably possible.
Security Concerns
There are numerous ways to secure an Access database. Perhaps the easiest and most common is to set a universal database password. All users will use the same password to open the database.
It is also possible to assign usernames and passwords through the Microsoft Access Security Wizard. Using this option, you can set specific permissions so that some users can or cannot gain access to certain functions.
One of my favorites is to use the Microsoft Operating
System’s native security features. Set
different logons for different people and assign different permissions. This way if the program is installed on a
“public” computer and different logins are assigned, the people on the public
computer will not even know the database is there. Once the Field Training Officers logs into
the computer, they will see the icon for the database and they can gain access
to it.
Electronic Signatures
Electronic Signatures are popular because you can eliminate the need for paperwork. The database manager creates a personal identification number for each person. All involved parties then enter their PIN into the form and that acts as their signature.
The
obvious concern with electronic signatures is their ability to become compromised.
Depending on how you program electronic signatures into the program
will give you varying levels of security.
An experienced Access developer could possibly gain the code at about
the same rate that an experienced forger could reproduce a signature.
This is hardly a concern in using Access for managing the field training
program.
If the correct PIN is not entered into the signature box, the database will not accept it. You will then be prompted to enter in the correct PIN. Only the correct PIN can be entered into the box.
Information Management
With all the information going into the database there needs to be an easy way to get it out and store it securely. You can leave everything in the program. That is an option. We (IUPD) are currently exporting a copy of the information out of the program and converting it to an Adobe PDF file. You must have a copy of Adobe PDF Writer to convert it or you can stick with the native Snapshot format (the snapshot format is the proprietary Microsoft format similar to PDF). The Snapshot Viewer is free and included with every copy of Microsoft Access. Both the Snapshot and PDF formats are secure. Unlike a Microsoft Word file, they cannot be modified or changed after they are created.
After we export a copy out of the database, we save it on CD and catalog it. The whole field training program for one recruit at IUPD comes to approximately 320 pages compromising over 4 megabytes. On the same CD as the performance evaluations, we save a copy of the Probationary Officer Manual, Field Training Briefs, and all the performance evaluations guidelines. We save them with the performance records because the field training program changes over time and we can only hold them accountable to the things that were taught to them at the time they went through the program.
We also leave the original copy in the database. This helps to compare current recruits to past recruits. If a problem comes up where a termination decision is questioned, the CD provides a convenient solution (imagine giving two binders of paperwork to an inquiring Administrator as opposed to a single CD – they are going to prefer the CD).
From the time of conception to our current stage of development on our database, approximately 9 months have passed. I feel that it still is not a completed product. The database is constantly evolving as new ideas to come light.
Conclusions
Microsoft Access is an invaluable resource for managing the field training program. A little know-how goes a long way in designing your own personal database.
If you have the time and you are interested in getting started, here are a few resources to get you going in the right direction.
Indiana University Police Department Field
Training Program – offers information on how we are tracking our Probationary
Officers and shows several sample reports - http://www.indiana.edu/~iupd/fto.htm
Microsoft Access Homepage
http://www.microsoft.com/office/access/
Microsoft Access Newsgroup
http://support.microsoft.com/newsgroups/
Microsoft Access Support
http://support.microsoft.com/default.aspx?scid=fh;EN-US;acc2002
Microsoft Access Security Page
http://www.nwidevelopers.com/Security.htm
The Access Web
Microsoft Access Help Center
http://www.access-programmers.co.uk/
About the Author
Christopher Dutton
is employed by the Indiana University Police Department as a second shift
Patrol Officer and Field Training Officer.
He holds several instructor certifications through the