contact@consultdmw.com

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:

Organisation chart for projects

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:

Organisation chart for project tasks and activities

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:

Organisation chart for project jobs and activities

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:

Activity tables in an Access database

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:

Project, task and activity data model


Defining Activities

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

Activity types tables in Access database

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


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:


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:

We'll use this query to compare dates:

Project task query design in Access

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:

Project task query datasheet

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:

Access select query design

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

Access query datasheet of project dates

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:

Query design for late-running projects

Query3's datasheet summarises the information we require:

Access late-running projects datasheet

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.

“I am as much interested in the smallest detail as in the whole structure.”

Marcel Breuer, architect and furniture designer (1902-1981)