Hi All,

I have a table with a number of projects (900+) with lots of milestone dates.

I want to be able to calculate which phase each project is in based on their dates.

Example milestones:

Kickoff
Phase 1 Complete
Phase 2 Complete
Final Complete
Go Live Complete

If KO has a date in the future (i.e. a forecast date) the project is in KO phase.

If Phase 1 complete has a date in the future, but KO has a past date, project is in Phase 1.

etc.

Can anyone describe a simple way to do this?

Thanks
dvent

Where do you want this information to show? A nested IF in your query might work. Add something like this as a calculated field:ProjectStatus: IF(fldProjDate>fldGLDate,"project is Live",IF(fldProjDate>fldFinalDate,"Final Beta",IF(fldProjDate>fldP2Date,"Phase 2",IF(fldProjDate>fldP1Date,"Phase 1",IF(fldProjDate>fldKODate,"Kickoff","Status Indeterminate")))))(warning: above pseudocode is untested)

I may have misunderstood your request, though. What exactly do the milestone dates look like, and when do you want to know their status - today when you run the report, or for each date?

Here is some example data

Project Name...Kickoff...Phase1...Phase2...GoLive
Project 1......... 01/01/09 01/05/09 01/08/09 01/10/09
Project 2......... 01/04/09 01/10/09 01/12/09 01/02/10
Project 3......... 01/01/09 01/02/09 01/03/09 01/04/09


We can see that Project 1 should show that it is in Phase2 (Kickoff has finished, Phase 1 has not yet)
Project 2 should show as in Phase 1
Project 3 should show as complete

Note: May data has alot more than 4 milestones so a nested IF statement is unlikely to work.

I want to have a column in a query that shows "Current Phase" so that I can use it in a number of reports.

Thanks
dvent

Is promotion to the next phase automatic, or does someone have to tickybox the database (actual vs. projected, basically)?

Also, please tell me that in your actual table structure, milestones is a separate table, not a series of [Phase1], [Phase2], .... [Phase37] fields.

the data i am using is a set of raw data from another database we use to track dates. project dates are all enterred into that.

my data has fields e.g. Go Live which has Actual Dates i.e. <Today and Forecast >Today all in the one field.

Does this make sense?

I would split your structure into 2 tables - 1 contains the main project details, the second contains your milestones per project - a one to many relationship. This way you can have as many milestones as you like without having to expand your project table structure - just keep adding to the items in the milestone table.

Now you can run a query (Group / Totals) linking your projects to the milestones for all milestone dates that have a finish date greater than today's date, and use the "First of Milestone Finish Date" to get the first date of the milestones that have not yet finished date and therfore also it's phase.

I would split your structure into 2 tables - 1 contains the main project details, the second contains your milestones per project - a one to many relationship. This way you can have as many milestones as you like without having to expand your project table structure - just keep adding to the items in the milestone table.

Now you can run a query (Group / Totals) linking your projects to the milestones for all milestone dates that have a finish date greater than today's date, and use the "First of Milestone Finish Date" to get the first date of the milestones that have not yet finished date and therfore also it's phase.

Hi Endre,

This sounds good in theory but I am a little lost in the practical sense.

Do you have 5 minutes to knock together an example couple of tables in access to attach to the post? No worries if not.

Thanks
dvent

Attached is my mdb. This is for example only - I would change things like the project milestone categories and versions e.g. - Forecast = 001, and have a Version Table, as well as a Milestone Table, rather than free text entry. Just didn't get a lot of time for this.

But you can see here we can have as many phases as required, and report on them as you requested.

001 query shows all projects aftyer today. 002 query shows current phase. Assuming that when one phase finishes, the next one starts.

Hi Endre,

I'm not sure how to get my data to be structured like yours. Do you have anything I can look at (tutorial etc) do achieve this?

Thanks
dvent

Couldn't open your mdb as it's 2007, my laptop is XP and 2003. Will open up yours on my desktop at home and convert and take a look. Give me 24 hrs....will make a conversion routine for you if I can.

Opened up your DB - Is your problem creating the tables or migrating the data across? Or is it something else? If you can let me know we can try to take this further, else I can't give you more than the .mdb I have attached to earlier message.

One thing I can suggest is that you scour this or other sites for details on building databases and training material. Else post a message requesting how other people have done / are doing the same thing and see how they are learning how to do things.