How to best show a monthly data profile??

[andy_dyer]

Hi,

I have searched high and low and can't find any inspiration...

I need to be able to within my database when creating a new project put in a proposed monthly profile based on what % complete I expect by month ends.

i.e. so may be July 09 = 25% August 09 = 50% September 09 = 100%

I will then go in and update this every month so the profile remains accurate.

At present I have about lots of fields from Jan 08 to Dec 09 and this will obviously grow...

I will only need one profile line for each project...

Is there any way of future proofing the database?

I experimented with a datasheet view as a subform but I still needed the fields and it was too wide...

I'm out of ideas and imagination...

Any suggestions welcome (please provide a little guidance of where to start - as they will almost certainly be things I've never done before...)

 

[gemma-the-husky]

if you only want to see each project in isolation then i would probably denormalize this, for simplicity

just add fields for

status1percent
status1date
status2percent
status2date
status3percent
status3date

this only becomes an issue if you want to do a query that says eg

"find all projects that are behind their schedule"

because now, a simple query won't be able to do this. Even so, it would be easy to write a function to evaluate the performance based on these - so speed wouldnt be a problem.

A properly normalised solution is probably more trouble than its worth,

 

[MarkK]

My 2c is a properly normalized solution is always worth it.

You propose storing the percent complete by period already summarized...

i.e. so may be July 09 = 25% August 09 = 50% September 09 = 100%

I would store pecent completed by month as follows...
7/09 = 25% 8/09 = 25% 9/09 = 50% (WHERE All = 100%)
This way the correct value is stored with the time period in which it occured.

 

[SOS]

I would normalize this and just add a row for each date (field name MILESTONE or something like that) so you can have as many as needed for as many projects as needed.

 

[andy_dyer]

Thanks everyone for your suggestions... not all of them make sense as i don't know what normalise means!

At present on my main table and as a result my main form I have Project name, number, value etc then fields called Jan 08, Feb 08 etc etc all the way to Dec 09 at present...

I don't knwo whether to split tables and/or use subforms or what really??

If anyone has a sample database they could paste to help me visualise what I can do - that would be fantastic!!

I do have other fields for a date and a % for how much it is complete as of this date... I would love this to automatically eb able to pick the correct % based on the date field and then looking up the right % based on the adte chosen...

I then would like to be able to calculate the difference between the last review date and the current review date %'s but i don't know where i would start on this - it is so depdendent on me getting the right structure at the start of this database...

 

[DCrake]

Andy I would not add fields as you are doing with month names. Some projects will take longer than others to complete. So some fields will never be completed. The amount of fields will always be determined by the longest project.

What you need is a new table (MileStones) that has the project Id as a FK and a period. Use this instead. You also need in your projects table an indicator as to how long is the project expected to take to completion. Say in months. So if a project has a completion period of 10 months then by month 6 it should be 60% complete. So if you compare the project start date with today in months and that works out to be 7 and the last project milestone date is Last month the the project is 1 month behind. Is that understandable?

David

 

[andy_dyer]

Thanks David for coming back to me - my problem is that none of our projects have that kind of flat profile we could be 50% of the way through in terms of time but only 10% of work to be done, then we leap from 10% to 60% in one month...

I do need some mechanism to input and track the profile we expect by months but just can't think of a better way - acknowledging my current method is very clumsy...

I can split this into a new table and link ok but I still think i need 36 fields to cover a three year period...

 

[DCrake]

Another thought would be to set up a kind of gannt chart and split the project up in to cubes whare by each cube represents a task and give each task a factor value. Then when each task is completed you should be able to total them up and come up with a completion value. Just a thought.

David

 

[andy_dyer]

I've had a think about this and i think that it more data than i want to input... these projects have up to 50 tasks and we have other software that tracks those...

I'm all out of ideas... I can think of somethings that may work for the display but not have the detail on a month by month basis for the reporting i.e. to do with relabelling 12 generic date field labels based on start date and duration; but nowhere will I be able to say on July 2009 the project was 30%...