Software

Track IT projects with this simple Access database

Managing an IT project often involves multiple teams in various locations. Providing status updates and tracking project changes can be a serious challenge. Build a basic Access database that makes it easy to enter project data and quickly build reports.


Project managers are often required to keep detailed metrics on a project's status so that others can understand the reason for delays or cost overruns, or the need for additional resources. The compilation of all of this data usually places a large demand on a project manager, who is often already squeezed for time.

Projects today are complicated, and development is often divided among many diverse and remote groups. How do you keep track of everything all these groups are doing? How do you keep on top of issues that are not your project team’s direct responsibility, but if the impact is not known, tracked, and understood, can escalate from an issue to a project risk? In the past, writing it on a piece a paper was good, or if you were very organized, you wrote it in a project logbook. But how do you disseminate this information to project teams, especially if they're in different locations? The reality is that in today's connected world, workers and managers want easy access to this type of project information.

The good news is that a simple Microsoft Access database can help. With its ability to create simple table structures, data entry forms, and reports, Access allows you to store and manipulate project data quickly and easily. You can use project documentation created in Access to improve project communication and highlight and resolve issues. In addition, Access is easy to set up and maintain, and it can provide a central tracking location for project issues. Access is part of the Microsoft Office suite of tools, and it's included with many versions of other Microsoft tools, applications, and software suites, which makes data easily accessible in programs that people are already familiar with. In this article, I'll show you how to create an Access database you can use to track IT projects.

Create a project log
Creating a table structure to hold a project log is simple. You can use the table as a central location for tracking project issues, action items, change requests, and risks. All members of the project team can enter information in a standard format, making reporting and reviewing the information easy.

Although any database management system will allow you to create the same tables and reports, Access is readily available in most customer environments. It can reside on a central repository (such as a file share), and you can make a copy to run on your own personal system (which is useful for the project manager who travels between locations, travels to visit customers, or occasionally works at home). Additionally, you can use Microsoft Excel to create the same format of data, and then append the data to the central database if a connection is unavailable.

To create a table to hold project log data, open the Access environment, choose Table under the Objects heading, and then choose New. Access will prompt you to create the table via a number of available interfaces. The Design View will allow a table-savvy user to enter field names and express their data types. Drop-downs for data types simplify the table creation. After entering all the fields you want for your project log table, save the table under an appropriate name.

Table A shows the sample project log table definition. Fields can be added or deleted to suit the user and the project. For example, you may want to add defaults to the fields or constraints to limit the values that project members can input. The index on this table is the Log Number field.

Table A: Project log table (columns)
Name Type Size
Type Text 1
Log Number Long Integer 4
Brief Description Text 255
Status Text 10
Priority Severity Text 1
Date Submitted Date/Time 8
Originator Text 50
Assigned To Text 50
Due Date Date/Time 8
Last Updated Data Date/Time 8
Progress Memo
Team Assigned Text 15
Additional Cost Currency 8
Component Text 50

Create project log forms
Now that there's a table to hold project log data, you can create a form to assist the project team in entering data. The simpler you make the form, the more likely your team will input data and provide you with valuable project status information. In Access, using a layout manager assists in creating a form. Access allows you to choose the table to insert, update, or query, eliminating the need to write complicated SQL statements (database code) to maintain the table and giving you the ability to make aesthetic changes.

From the main Access menu, click on Forms and then choose New. Select the Design View and the table you want to obtain data from; then click OK to enter the form layout. From this point, you can use the Tools menu to add and arrange the objects on the form. Figure A shows a sample form.

Figure A
The Access form provides a user-friendly way for team members to enter information in the project log.


The project team members can use this form to document issues and actions as they are identified during the life of the project. As project log entries are addressed and resolved, the project log also becomes a valuable history of the project's progress.

Project log reporting
Throughout the life of the project, you can create a wide variety of reports to review and analyze the project log data. You can also develop reports to provide project metrics to customers and management.

Consider using a standard project report to review during team meetings. Important action items will not be lost or overlooked when they are documented and reviewed on a regular basis. Project team members can use the information in the log to communicate the status of issues to all members of both the local and remote project teams.

As in the forms creation process, the reports creation uses a layout manager that gives you the ability to drag and drop objects within the reporting area. Tags are automatically created for each piece of table data, but the name can be easily changed to something more meaningful. Titles, page numbers, headers, and footers can all be added to give your reports a professional appearance.

To set up a report from the main Access menu, click on Reports and then choose New. Select the Design View and the table you want to obtain data from; then click OK to enter the report layout. You can now use the Tools menu to add the objects and arrange them within the body of the report. Figure B shows a sample project log report.

Figure B
Access lets you set up reports that can present project log data in an easy-to-read format.


Maintaining the project log data within a database structure lets you easily create reports based on the information that is most important to your project team, such as:
  • Project log summary
  • Project actions, issues, risks, and change requests
  • Project metrics (percentage of open and closed issues, and number of change requests issued)

As an alternative to creating Access reports, you can simply extract the data in your project log into an Excel spreadsheet.

Take control
It's time to collect all the pieces of paper you've used to write down various project issues and consolidate them into a simple Access database. When you do, you'll have a better picture of the issues, outstanding action items, and project risks. You'll be able to create meaningful reports for your project team, management, and customers, and keep everyone informed of the current project status. Ultimately, you can give your coworkers and customers the metrics they demand.

Editor's Picks