Planned, forecast and actual dates

[Enzo.F]

Hello,

My objective
Create an empty db with pre configured tables, queries, forms etc... That can be applied to different projects, just requiring each project to enter their own specific data.

Database functionality
Essentially it is a planning/activity tracking db. In my world each project is comprised of mini sub projects. The objective is to track and record the progress of these sub projects. Each sub project has a number of activities and these activities have three key dates that need to be recorded. A planned, forecast and actual. Each forecast activity date is dependant on the dates of the previous activity. So the forecast of activity 2 is dependant on the forecast or actual date of activity 1. Planned dates usually remain static thought the duration of the project.

There will be pre set durations between activities to assist with forecast dates. However, these are not always common as some sub project are more complex than others. On the plus side the activities are common for all sub projects with in a project. On the negative side each project (db) will have a unique set and number of activities for its sub project.


The issue
Without knowing the the activities and various durations in advance. how can I create a shell db that will calculate the forecast dates. I have previous produce a db that successfully achieved this but I knew the activities and all duration periods in advance and was able to hard code the calculations into the various fields in the queries. However, this is not ideal when wanting to apply it to new projects.

Hope this makes sense and any assistance would be greatly appreciated.

 

[plog]

So the forecast of activity 2 is dependant on the forecast or actual date of activity 1...However, these are not always common as some sub project are more complex than others...On the negative side each project (db) will have a unique set and number of activities for its sub project

You've got too many moving parts for me to understand exactly what you are trying to do. I get the gist, but it doesn't sound like everything is the same from project to project. Can you post some sample data?

 

[jdraw]

Just a thought since your profile is showing this as your first post --
Have you considered a Project Management software package?
This sort of functionality and much more is built in, and concepts like Work Breakdown Structures, Gantt charts etc are designed into such software. It just seems a complex undertaking, and possibly the wrong software especially if you plan on building this from scratch.

I'm not suggesting you can't or shouldn't do such a project, however, it isn't a trivial undertaking, and there are many commercially available packages in the marketplace.
You may want to do some sort of cost benefit/feasibility to rationalize your decision.

Good luck with whatever you decide.

This may be of interest to you
http://www.pcmag.com/article2/0,2817,2380448,00.asp

PS: I do not use any of these. I'm retired.

 

[Enzo.F]

Can you post some sample data?

Correct, there are many unknow and moving parts.

For the end result i am trying to achieve the below:
Sub ProjectActivity 1 - PlannedActivity 1 - ForecastActivity 1 - ActualActivity 2 - PlannedActivity 2 - ForecastActivity 2 - ActualActivity 3 - PlannedActivity 3 - ForecastActivity 3 - ActualSP102 June 201302 June 2013 07 June 201307 June 2013 17 June 201317 June 2013 SP201 April 2013 01 April 201306 April 2013 09 April 201321 May 201324 May 2013

The forecast date become irrelevant once the activity has actualised.

As the activities are unknown in definition and quantity, i have set up a table identify them.

the durations between ativities are also handled in a seperate table, which i have called chains. as one set of durations (or a chain) can be applicable to more than one sub project. this table looks like this:
Chain idSequence NoFromToDurationC11Activity 1Activity 25C12Activity 2Activity 310C13Activity 3Activity 45C21Activity 1Activity 25C22Activity 2Activity 345C23Activity 3Activity 45


i hope this helps identify my problem a little more?

 

[Enzo.F]

Have you considered a Project Management software package?
QUOTE]

Thanks for the suggestion. I have considered it. However, many of the free applications are primary web facing and would not be permitted by our IT organisation. Additionally, from previous experience, it is extremely challenging to get new software in to our organisation. This is why I was trying to utilise Access. We currently do this in Excel. However, it becomes difficult to manage, update and perform trend analysis on.

We do use P6 within our organisation, but the general consensus is that it is perhaps a bit heavy handed for this low level tracking. Something, that is on the agenda to re discuss.

Thanks again, especially for the article. Interesting read.