Author: Neville Turbit

Download Sample Access File (Access 2000 or later)

Overview

Most of us have struggled with the limited reporting facilities on Microsoft Project. You never seem to be able to find the report you want. There is another way. By saving the Microsoft Project file as a Microsoft Access file, you can write your own reports. Saving as Access does not replace your traditional .mpp file. It creates a second file with an .mdb extension.

Note: Project 2007 does not provide this facility. You need to be using an earlier version.

Save to Microsoft Access

Go to the “File” menu and select “Save As”. Under “Save as Type” select “Microsoft Access Database *.mdb”. This does not replace the .mpp file. It creates a second file.

What linking does is that you have an Access file (your new database) where you can write queries and reports, but the data is in another file – in this case the Access version of the Microsoft Project file. You can tell it is linked if you look at the tables. They have an arrow to the left of each table. You can delete the links without deleting the files.

Now you are linked, you can write your own reports.

Key Tables

One of the key tables you will use is the Task table. MSP_TASKS contains all the details regarding the tasks in your project such as name and duration. It also contains milestones which are tasks with a zero time:

Fields are:

Key Fields

It is not quite as daunting as it looks. You will find most of the fields are blank when you look at the table. A few key fields are

• TASK_NAME which is the name of the task
• TASK_OUTLINE_NUM which has the number of the outline just as you might do with numbering headings in a report e.g. 2.2.3.
• TASK_START_DATE
• TASK_FINISH_DATE.

You can open the table and look at the data to get a better understanding of what is recorded.

Create a Query

By just using the query wizard in Access you can write a report that lists all the tasks in finish date order.

In Criteria, we put 0 under TASK_IS_SUMMARY and <>100 for TASK_PCT_COMP. In other words, the task is not a summary task. and it is not 100% complete.

SQL

The SQL statement looks like this:

SELECT MSP_TASKS.TASK_START_VAR, MSP_TASKS.TASK_IS_SUMMARY, MSP_TASKS.TASK_OUTLINE_LEVEL, MSP_TASKS.TASK_START_DATE, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_PCT_COMP FROM MSP_TASKS

WHERE (((MSP_TASKS.TASK_IS_SUMMARY)=0) AND ((MSP_TASKS.TASK_PCT_COMP)<>100))

ORDER BY MSP_TASKS.TASK_FINISH_DATE;

For those who are not familiar with SQL, the key components are that you:

• SELECT a number of fields to output for use in a report. Each field is separated with a comma
• Define the condition WHERE. The condition is that the field TASK_IS_SUMMARY is 0 which means it is not a summary task. The second condition is that TASK_PCT_COMP is not 100%. It is not complete.
• Set the ORDER.

Write the Report

Now you have the query, you can write the report. Use the report wizard and you can easily put together a report. This report is useful when you want to see on one page, what is due for completion in date order. It will help you focus on the immediate tasks. I have not been able to find anything in Microsoft Project that gives me this simple information. The report might look like this:

Conclusion

Many people complain about the limitations of Microsoft Project in terms of reporting however by saving it as an Access database, you can do whatever you like with reporting. Whilst this simple report only uses one table, you can link tables and do all sorts of complex reports to suit your own requirements.

Copyright Project Perfect