I was brought in by a client to develop a solution that would capture the company’s human resource data. All of their data was stored in Excel worksheets. General information—name, Social Security number, and address—was stored in a master spreadsheet.
Other spreadsheets stored salary history, training history, and benefits. The employee ID was used as the key to relate lines in different spreadsheets back to the master sheet. All additions and changes were made manually.
This approach had several problems, which the client wanted resolved in the new application. The biggest problem concerned data reporting. Running reports across multiple spreadsheets was difficult. Creating a simple report, one that would show all employees currently on vacation, for example, involved sorting and filtering the data in several spreadsheets, copying and pasting the results to a single worksheet, and adding formatting to improve the presentation. It could take over an hour.
The second problem was one of data integrity. Because all the spreadsheets were maintained manually, data entry errors frequently caused the employees’ records to become inaccurate. For example, a mistake entering the seven-character alphanumeric character employee ID would cause all the related records to lose their relationship to the master list.
Other improvements the client desired were the ability to run ad hoc queries on the data and to access the data from up to four workstations without having to copy the spreadsheets to the workstations manually.
First of two parts
This is the first of a two-part series that describes how consultant Evan Stein used Microsoft Access to develop a database client solution. The next installment in this series will explain how he linked the Access database to Visual Basic code to handle complex calculations and integrate the database with Microsoft Word templates. We’ll also see how he moved the application to the Internet using Data Access Pages and Active Server Pages.
Designing the solution
I noted the major design points of the client’s desired solution:
- The solution was strictly a data management and reporting application.
- The application had no workflow or business processes in it. Data would be entered, edited, deleted, and reported on.
- It needed a single, easy-to-use interface for all data entry and management.
- The client needed to create ad hoc reports.
- Some programming would be necessary to automate certain features of the user interface (fields that would change based on selections of other fields).
- The solution needed a good reporting engine that would allow users to build simple and complex queries easily and apply a consistent, professional-looking layout to the reports.
- The solution needed to support multiple, simultaneous connections.
I recommended Microsoft Access for several reasons. First, it supports all of the above needs. With a properly designed and created database, development of a UI to enter and manage data is simple and straightforward. Included templates can be used or custom ones can be created to provide a consistent look for the interface.
It also supports Visual Basic and has the ability to link to COM objects, which provides powerful programming capability and keeps the application easily open to future enhancements. Access also has a powerful reporting engine that is easy for end users to learn and master.
While other database tools also provide these features, I chose Access because of the abundance of support available for Microsoft products. The Microsoft Web site provides extensive, free support for developers and end users. There are also numerous Web sites and discussion groups, and a plethora of books and magazines that provide training and support for Access.
That’s not to say I don’t recommend non-Microsoft solutions when appropriate. However, I find that when features and cost are comparable, most of my clients are more comfortable with Microsoft products because of their dominance and support options.
Defining and creating the database
The first step in the development process was to define all the data elements and create a data dictionary to store all the definitions. Through discussions with the client, every data element was identified and the criteria for each element were documented. Some of the criteria were:
- Name: How the element would be referred to in text and conversation
- Displayname: How the element would be named on the user interface
- Display type: Text field, radio button, drop-down box, etc.
- Type: Short text, long text, integer, floating point number, yes/no, etc.
- Type criteria: Included upper and lower bounds for numeric elements, length of text elements, where the element could be undefined (null), etc.
- Valid values: The acceptable values for a listed element
Figure A shows some of the database attributes.
When all the elements were defined, I began laying out the screens. I was going to create several data entry forms, so I worked with the client to group related data elements and define the relationships between the visual controls on the forms. I created the screens as graphics files using JASC’s PaintShop Pro, not as Microsoft Access forms. I didn’t want to create the forms until the database was created, but I wanted the client’s approval on the look and feel before getting started with the development.
I laid out each screen and showed them to the client in a presentation that followed the user’s interaction with the application. This ensured that the way in which I was designing the application matched the users’ daily business needs.
I then began creating the database. I created the lookup tables first. I created a lookup table for every field that contained a predefined set of values (department names, job titles, and office locations). This would allow the client to easily manage the items that would be displayed in various drop-down boxes on the Access forms. To simplify uniquely identifying each row, I used an autonumber field to define the primary key for each table.
Next, I started on the main tables. In Access, each database field has a number of supporting attributes that can be defined. For each field, I defined:
- The field size.
- The format.
- An input mask to control the format of user input.
- The field caption (used when the forms are created).
- The default value.
- Field validation rules.
- Allowance/disallowance of zero-length values.
The final step was to define the display control and any supporting values. I used list boxes and combo boxes for any fields that had a predefined list of values. These values would be stored in the lookup tables I created previously. I defined the Row Source Type as table/query and the Row Source as the appropriate lookup table.
Access automatically incorporates support for each of these attributes into the user interface without any additional development.
Once the tables were done, I defined all the database relationships. Relationships define the associations between tables. For example, the employee table had a department ID field, which was a numeric value. The department lookup table had a matching department ID field and a department name field. (This is standard database design practice.)
After I defined the relationship between the department ID field in these two tables, Access would automatically replace the numeric department ID field with the more user-friendly department text field wherever appropriate. To add a relationship, I selected Tools | Relationships from the main menu. This opened a window showing all of the database tables. I selected the specified field from one table and dragged it over the corresponding field in the other table.
Creating the UI
I started on the user interface by creating a format. In Access 2002 (from Office XP), you do this by creating a template form with the color scheme, title, and other common visual elements that you want throughout the application. Then, I selected Format | Autoformat from the main menu, clicked the Customize button, and created a New Autoformat based on the Current Form. This format was then applied to all the forms, giving a consistent look and feel to the entire application. Figure B shows how the main screen looked.
I created each form by following the same steps. I created a new form and set the Record Source property to the corresponding table. Then, I built the form by dragging each field that I wanted to display from the Field List window to the form and positioning it where I needed it. All of the attributes that were defined when the table was built, such as the input mask and validation rules, as well as the control type and its caption, were automatically added by Access.
By building forms using this method, I saved time, kept data elements consistent across multiple forms, and kept all the field attributes contained in a single location (the database table rather than the form control definition).
I created forms that needed to display master-detail records using subforms. In this application, each employee in the database could have an unlimited number of performance evaluations. The employee table would be the master table and the performance table would contain the detailed records. I created a form and linked it to the employee table. On it, I displayed the employee’s name, ID, and department. Using the Control Wizard in the Toolbox, I dragged the subform control onto the master form and selected the corresponding detail table (in this case, the performance evaluation table) from the Control Wizard dialog box. When the application is run, the relationships that I defined while creating the database automatically display the correct detail records for the current master record.
Data management and navigation
You may have noticed that I haven’t written any code yet for record navigation, data entry, and data management. That’s because Access provides all this for the application by default. The ability to create and save a new record, change the data of an existing record, delete a record, move through a recordset, and filter and order the records to be displayed is all provided by Access without requiring any additional development. If necessary, each of the features can be customized or overwritten by writing Visual Basic code and linking the code to the appropriate controls.