Project Management DB - Maintain Milestones

[mkallover]

I have a project management database that I have created for my company that helps us track our IT projects. One of the things that business owners use the DB for is tracking project milestones (e.g. completion of design, coding, QC, etc.) I have a separate milestones table that is linked to the main project table by the project number.

My company works on a monthly IT release schedule and the milestones for each project are based off the release date. IT maintains a table that contains all of the expected dates for each project phase. If a project gets delayed for some reason all of the dates of the milestones get pushed back.

What I would like to do is create a table in my database that would store all of the milestone dates so that as they get updated, each project's milestone dates will get updated.

I'm just trying to wrap my head around the best way to accomplish this. I have separate entries in my tblMilesones for each phase of the project and all I really want to link is the "TargetDate" field to pull the target date out of the Release date table.

Did I explain that well enough for everyone to get a visual? Does anyone have any ideas how this could be accomplished most effectively?

 

[fneily]

Since the milestones are calculated from the release date, then you'd only store one date and calculate the rest in a query/form/report. If a project is delayed, you'd just add the appropriate amount of time to the calculations.
You'd need to add a field to track the amount of time delay for each project to recreate the timeline in the future.

You can also create a timeline report that shows graphical bars representing the various phases based on these dates, if you're interested.

This way follows the protocols of normalization. Keeping dates that can be otherwise be calculated goes against normalization.

My opinion.

 

[mkallover]

Unfortunately the milestones aren't calculated solely on the release date. They base the milestone dates on other factors as well so there isn't a good way to predict the milestones based on the release date. Many times they factor weekends, holidays and other factors into assigning the milestone dates.

 

[fneily]

Got confused with "milestones for each project are based off the release date."

Can you link to the IT table from your Access database?
How do you pull in the original dates?

 

[MajP]

If I understand correctly
I have a table with projects and target releases

tblRelease
projectID
targetDate

projectID	targetDate
1	         12/30/2009
2                1/10/2010

I have a milestone table with project ID and milestones.
[/code]
projectIDfk designDate unitTest componentTest
1 12/30/2008 8/18/2008 9/17/2008

---continued---

alphaRelease betaRelease finalRelease
11/6/2008 11/26/2008 12/30/2009

If I want to update the schedule based on a new target date for project1

qryUpdateSchedule
[code]
UPDATE tblRelease INNER JOIN tblMilestones ON tblRelease.projectID = tblMilestones.projectIDfk SET tblMilestones.designDate = [targetDate]-365, tblMilestones.unitTest = [designDate]+200, tblMilestones.componentTest = [unitTest]+30, tblMilestones.alphaRelease = [designDate]+280, tblMilestones.betaRelease = [designDate]+300, tblMilestones.finalRelease = [targetDate]
WHERE (((tblRelease.projectID)=1));

where we design 365 from target, go to unit test 200 days after design, component test 30 days after unit test....

 

[MajP]

If I change the target release date for project 1 to

10/30/2010

the above query does update the new schedule to
projectIDfk designDate UnitTest componentTest
1 10/30/2009 7/18/2009 8/17/2009

alphaRelease betaRelease finalRelease
10/6/2009 10/26/2009 10/30/2010

Now these are notional dates that you can edit to the real dates.

 

[fneily]

It seemed to me that those number of days that are to be added are arbitrary and he won't know those until, let's say, minutes before he does the update. And they'll be different fot every project. Let's say something really bad happens and all the projects are delayed with their own different delay times for each individual phase. Quite a mess.
Hopefully, it's as simple as what MajP presents.
That's why I wonder since the IT department is doing their updates, can he just directly use IT tables.
Have to wait to see what he says.

 

[MajP]

If it is that complex, than at that point I would drop down the 150 dollars for MS Project and you could import from the IT database. Then you could define complex phase dependency and really manage the process.

 

[fneily]

Ah yes, Project. Forgot about that. You're right. Still waiting for clarification.