This article has been
reprinted from TechProGuild. If you find this article helpful, subscribe to
TechProGuild to get access to all our in-depth technical articles. Sign
up now for a FREE 30-day trial. All the articles on our site that include a
green $ icon are available only to TechProGuild members.
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
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
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.
|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
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
|Access lets you set up reports that can present project log data in an
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:
actions, issues, risks, and change requests
metrics (percentage of open and closed issues, and number of change
As an alternative to creating Access reports, you can simply
extract the data in your project log into an Excel spreadsheet.
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.