By Michael Jenkins
Project management software is designed for projects with many tasks to be accomplished from beginning to end by the same person, team, or department. Software development projects, however, are typically more random in nature. Developers jump around a lot. They often work on a task until they reach a roadblock. A roadblock can be the need for another module or more information. Instead of waiting for the roadblock to clear, developers may move to another task until the roadblock is resolved. Thus, many developers tend to multi-task throughout a software development project.
This article shows how to use Microsoft Access 97 to build a system that manages the ongoing customization of a commercial software product. This approach is perfect for anyone who wants to develop databases for marketing, or for managing any kind of project where the tasks and their dependencies aren't entirely sequential. A system based on these concepts has been used to manage software development and customization projects in excess of one million dollars. The Access 97 tool, along with a strong leadership team, competent developers, and well-defined functional requirements, is an elegant solution to a real-world problem.
The Project
Commercial project management tools such as Microsoft Project are excellent for building the initial project plans, Gantt charts, and resource requirements. However, the automatic resource management features don't model reality once activity begins. I have not yet found a project management tool that can accurately level resources, adjust task priorities, and calculate a realistic end date for the work once the first hour of actual work has been added to the project plan. We use project-planning tools to build the initial tasks, set up the dependencies, and level the resources to create the initial schedule and proposal. Once this is done, the tasks, hours, and resources are transferred into the Access 97 tool described in this article.
To be effective, the goal of this methodology is five-fold. It must:
The solution is an Access 97 database that manages schedules, resources, and tasks. The database application provides user-friendly data entry forms for resources, tasks, and activities. It uses queries to quickly report summary and detailed project information. Finally, it summarizes and illustrates some powerful features of Access 97 development.
The suggested software project management methodology involves tracking budgeted resources against actual resources used. This article builds reports that show the hours required and hours used for many activities. Because each of these activities is assigned to a resource, the typical project management pattern is to make sure there are more resources than work assigned. Although this method doesn't provide an easy means to schedule start and end dates or predecessor/successor relationships, it addresses the random work order of software tasks much more effectively. In my experience, the best use of the methodology is to build the initial plan using project management software. Once the plan is built and resources are leveled, the data is transferred into the Access 97 Project Manager for continuous project tracking.
Step by Step: The Database
Managing a project involves the interrelationships of data. Fortunately, this is the purpose of a database management system such as Access 97. To get started, open Access 97, create a new, blank database, and name it "Project Manager."
Create several tables to hold your information. Begin by creating the Resource table as described in FIGURE 1. Next, create the Task table as described in FIGURE 2. Each task will consist of one or more activities, so create the Activity table as described in FIGURE 3.
Now, describe the relationship between the three tables. Relationships are one of the most powerful features of Access 97. Display the Relationships Window by clicking the Relationships button on the toolbar, or by selecting Tools | Relationships. Select all three tables, then organize them as shown in FIGURE 4. Connect resource.Initials to OwnerInitials in the other two tables. Then create a one-to-many relationship between tasks.TaskID and activities.TaskID. Double-click the line to open the Relationships dialog box.
This enables Access 97 to quickly determine links between tables and fields when creating forms and reports. The one-to-many relationship between Tasks and Activities causes a deleted task to delete all of the activities associated with the task. The three primary tables are now complete.
The Forms
Forms provide intuitive screens to create, modify, and delete information. Since we have three primary tables, we will need three forms to help the user work with the information in these tables. This article uses the Form Wizards to demonstrate the power and ease built into Access 97.
Select the Forms tab, then create the resource form using the Form Wizard. Select New Form, Form Wizard, and select resource as the table. Then select all the fields, and the Tabular format for the form. Finally, select a style and name the form Resource. Edit the design, change the Name and Availability field lengths, etc. until the form appears as shown in FIGURE 5. (The entire database is available for download; see end of article for details.)
Next, use the same steps described above to create a tabular form for the activities table. Remove the ID field, and modify the layout as shown in FIGURE 6. Use the property editor (right-click on the screen and select Properties) to delete the background graphic, then select a Background Color that blends with the International style (this example uses a dark cyan).
Right-click the OwnerInitials field to change the type to Combo-box. Click the data tab, then at the Row Source entry, create a query based on the resource table that includes the Name and Initials field (see FIGURE 7). Click the exit button at the top right corner and save the query. Set the Bound Column entry to 2. Select the Format tab and set the Column Count entry to 2. This causes the combo-box to display the name in the entry area, but use the Initials field as the key.
Click the EstimatedHours text box. Hold down S and click the ActualHours text box and Completed checkbox. Press CC to copy these to the Clipboard. Expand the footer section and paste these directly below the original fields.
Select the Data tab and set the Control Source for each entry to the formulas as follows:
- EstHrs — =Sum(EstimatedHours)
- ActHrs — =Sum(ActualHours)
- SumComplete — =IIf(Sum(Not[Completed])=0,True,False)
These formulas automatically summarize the Estimated Hours and Actual Hours values. Since True values for Yes/No field types equal -1, and False values equal 0, the Not[Completed] formula inverts all -1s to 0s, and vice versa. Taking the sum of all (Not True) values will equal zero only when all the values in the Complete variable are True. Drag the corners of the window in until only the display is shown, then save the form as Activities.
Finally, use the form wizard to create the columnar Tasks form. Select all fields except TaskID. Move and stretch the fields until you get the layout similar to FIGURE 8. Change the field type of OwnerInitials to ComboBox, set the Row Source and Bound Column fields to display the resource name like the Activities subform just created.
Insert a subform field across the bottom of the form. Select the Forms option and enter the Activities table. Select the link type by TaskID. Then click Next, set name entry to Tasks and click Finish. Click in the subform window, select the Events tab and add this code to the OnExit event:
Private Sub activities_Exit(Cancel As Integer)
Me![EstimatedHours] = Me![activities]![SumEst]
Me![ActualHours] = Me![activities]![SumAct]
Me![Completed] = Me![activities]![SumComplete]
End Sub
This will cause the task-level fields to update from the sums of the activities whenever you exit the Activities subform.
The data entry forms are complete. Open the Tasks form and create some data.
The Reports
The essence of project management is interpreting the current project status from the available data. A comprehensive set of management reports will facilitate this interpretation. Begin by creating a subreport that will be used to illustrate the progress of all activities described in the Activities table.
Select the Reports tab. Create a new report, select the Report Wizard for the Activities table. Then select all fields except TaskID. Choose the defaults until you arrive at the Layout page. Select Tabular and Portrait options. Choose a report style that is appealing (I used the Casual Style).
Name the report activities subreport, choose the Modify the report's design option, and click the Finish button. Modify your report layout until it looks similar to that shown in FIGURE 9. Right-click on the gray area outside the report detail section. This selects the report properties instead of the detail item properties. Make sure the properties dialog box is open. Select the Data tab, the click on the Record Source entry. This will cause the down arrow and ellipses (...) box to appear. Click the ellipses and click Yes at the prompt to create a query based on the table.
Add the resource table and copy the fields into the query builder. This provides the full name on the report instead of the initials. Save the query by selecting the close window icon in the upper-right corner. Click the field showing OwnerInitials and change the control source from OwnerInitials to Name. Test your report by clicking the Report-View icon in the upper-left corner of Access 97.
Now, create the Task Detail Report by selecting the New, Report Wizard using the Tasks table. At the fields entry screen select all fields except TaskID. At the grouping panel, select the DueDate field to group the report by delivery due dates. At the sort order panel, sort by Priority. At the layout panel, select Outline 1 and Portrait. The completed report is shown in Figure 10. The Task summary report is created in a similar manner (again, the entire .mdb file is available for download).
Finally we will create the resource budget vs. actual report — the cornerstone of the Project Manager methodology. The final report shows the resource, hours allocated, hours available, date due, and calculated earliest completion date. If the date due is earlier than the completion date, you have a potential resource shortage. The finished report is shown in FIGURE 11.
The Switchboard
Access 97 has an add-in feature called the switchboard. This is an automatic menu program that becomes the control panel for your application. Begin by creating the switchboard for the application. Select Tools | Add-Ins | Switchboard Manager from the main Access menu. The final switchboard is shown in FIGURE 12.
The Wrap-up
The Project Manager database was written to show some real-world examples and techniques that highlight the power of Access 97. It provides a strong foundation for a comprehensive project management system. The concept described here has been substantially embellished. Some of these enhancements include:
Download source code for this article here.
Mike Jenkins is a Director at Kurt Salmon Associates, an Atlanta-based company. Mr. Jenkins leads teams of developers who customize DCMS®, KSA's flagship warehouse management system. Mr. Jenkins has over 15 years experience in the development and management of sophisticated software applications. Kurt Salmon Associates (KSA), is the premier global management consulting firm specializing in Consumer Products, Logistics, Information Technologies, Healthcare, Corporate Finance, and Retailing. To contact Mr. Jenkins at Kurt Salmon Associates call (404) 892-0321, or send email to mjenkins@kurtsalmon.com.