dmw | consultancy
CONTACT
tel 01732 833085
tonbridge · kent · UK

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 2018-10-15 by David Wallis.


Preface

My objective 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:

Org chart

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. Party data model 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:

Task activities

Alternatively, if your project is laying a length of dual carriageway, 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:

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 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:

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:

Activities lookup

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 query

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:

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:

Query MAX

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

Query MAX datasheet

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 late project

Query3’s datasheet summarises the information we require:

Late project 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;


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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.

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

Marcel Breuer (1902-1981)