Microsoft® Access Database Tables for Project Management

Data tables and their relationships for a Microsoft Access database for recording who does what and when — for projects, tasks, jobs and activities.

Last updated on 2024-05-19.

Preface

The objective here is to provide a general-purpose module to manage who-does-what-and-when that you can incorporate into a database.

The module reflects this notion of a project:

The module treats an activity as something undertaken by a person, a group of people or an organisation in order to complete a task.

I'll be using the entity party as a coverall for people, groups and organisations.

My How to Apply the Party Data Model to an Access Contacts Databases web page introduces the concept of the entity party and how it can be incorporated into a database. Tables on that page dovetail with the tables introduced here.

If your business does not demand as much detail, you may consider my proposed structure is too top heavy and that this model would be best:

Alternatively, if your project is converting a dumb motorway into a smart one, you may need an extra layer of detail between task and activity. If you do, this model still works — all you need do is add an additional table into the schema:

You don't have to use the same terms as I do. Make your naming convention your own, and title your tables accordingly. My experience is that names work well when they imitate their usage in the business for which the database is created.

Access Database Tables

Whichever of the above schemas you use for your database, this is representative of the corresponding Access tables and their relationships:

In the tables above, the party identity relates to the person, group of people or organisation assigned to perform the activity. In the following, the party is also assigned to the project to identify the party for who the project is being undertaken:

Defining Activities

You'll certainly want to identify the type of activity being performed. I use a look-up table to do this:

My reasons for using a look-up table, in preference to putting a free-form text or memo field include in tblActivity, are these:

• The database administrator has control over the choice and exact description of each activity. This would not be the case if you used a note or memo field for the users to make up their own type descriptions for activities
• The activities in the look-up are easily made available to the user through a combo box
• Sorting, filtering, analysing and reporting according to types of activity should be straight forward, consistent and accurate. You'd find this very difficult to achieve if your activities were recorded as free text.

Date Fields

In the table illustrated on this page there are date fields for both tblProject and tblActivity. These are some of the ways you could use these:

• tblProject.DateFrom The start date for the project.
• tblProject.DateTo The scheduled completed date for the project. Or you could leave this blank to indicate that the project as a whole is on-going or that the completion date has not been finalised.
• tblActivity.DateFrom The start date for the activity, either actual or scheduled.
• tblActivity.DateTo The date on which the activity is completed or is due to be completed.

Project is Running Late

A key piece of data that your database needs to provide is whether a project is on time or running late. In this section I give some guidance on working your dates to yield this information, considering that sometimes certain of Access's idiosyncrasies can lead to incorrect results if you're not aware of them.

These factors determine the condition for a project to be late running:

• There is a date assigned to the tblProject.DateTo field
• The latest of the dates assigned to tblActivity.DateTo is later than that tblProject.DateTo.

We'll use this query to compare dates:

Let's imagine this query yields data about three projects that start in different years and one of which has no end date. Each project has three activities in all. Activities in all projects have a start date. At least one activity per project has no end date:

Here's a second query in the design of which the Totals are shown. Group By is the default for Totals. In this query tblActivity.DateTo's Total is set to Max:

Run this query and Max latches onto the latest activity end date for each project:

Now to identify a late-running project we need the query to compare the two dates. To achieve this we tweak the design and add an column. The tweaks — necessary to make the new column work — make the output of the query easy to understand:

Query3's datasheet summarises the information we require:

Only project Unus is picked out as running late. Duo is not because all three of its activities lack end dates. Tres is not because it does not possess an end date.

The expression IsLate: [ActivityEnd]>[ProjectEnd] is a condition test. A result of -1 is equivalent to TRUE or YES.

It's remiss of me to not have included project quattuor for which all its Activities were completed within the scheduled project period. Had I done so, IsLate would be showing 0, equivalent to FALSE or NO. Despite this ommission, the query does what we want — return a -1 to identify those projects running late.

Here's the SQL for Query3, if you want to copy it into your query design:

SELECT tblProject.Project, tblProject.DateTo AS ProjectEnd, Max(tblActivity.DateTo) AS ActivityEnd, [ActivityEnd]>[ProjectEnd] AS IsLate FROM (tblProject INNER JOIN tblTask ON tblProject.ProjectID = tblTask.ProjectID) INNER JOIN tblActivity ON tblTask.TaskID = tblActivity.TaskID GROUP BY tblProject.Project, tblProject.DateTo;

Scheduling History

If you need to track schedules against actuals for activities, a simple scheme is to have fields for both scheduled and actual dates: DateScheduledFrom, DateScheduledTo, DateActualFrom and DateActualTo, for example.

If it's required that your database be equipped to track mulitple changes to actual and scheduled dates throughout a project's history, I recommend you resist creating more date fields in the existing tables. Instead, introduce additional tables specifically for dates. Establish a one-to-many relationship between tblActivity and your additional tblActivityDates.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.