By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 454,335 Members | 1,791 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,335 IT Pros & Developers. It's quick & easy.

# Store the Value?

 P: n/a I have been reading a few topics on storing data in a table and how it is usually a bad idea. Can someone give me a clue on how to do the following: I am tracking Earned Value for my programs. Earned Value takes into account the budget * % complete. Therefore it calculates how much the manager says is done on the task by the budget. Easy enough; but then the next month comes and I still need to know what the Earned value was for the previous month for graphing purposes. Example follow: January Task % Complete Budget EarnedValue A 20 100 20 Skip to next month February Task % Complete Budget EarnedValue A 40 100 40 For graphing purposes i need to keep the EarnedValue for January as 20 and February as 40. Is this possible. The %complete changes on the task so I can't calculate January anymore. Is this a good time to store the value? Thanks for your help. Keith Nov 13 '05 #1
Share this Question
12 Replies

 P: n/a Well, either you have to store the earned value each month or the % complete each month. Depends to a certain extent if the budget changes, (have you ever known a budget not go up) in which case you need to store that as well. If data needs to be stored - so be it. I think you are getting confused with storing calculated values, which by and large is a bad practice. I would store in the monthly table the budget (only if it changes otherwise it could be stored in the project table, and store the %complete in the monthly table. You than can do the earned value as a calculation which is not stored HTH Phil "turtle" wrote in message news:11*********************@f14g2000cwb.googlegro ups.com...I have been reading a few topics on storing data in a table and how it is usually a bad idea. Can someone give me a clue on how to do the following: I am tracking Earned Value for my programs. Earned Value takes into account the budget * % complete. Therefore it calculates how much the manager says is done on the task by the budget. Easy enough; but then the next month comes and I still need to know what the Earned value was for the previous month for graphing purposes. Example follow: January Task % Complete Budget EarnedValue A 20 100 20 Skip to next month February Task % Complete Budget EarnedValue A 40 100 40 For graphing purposes i need to keep the EarnedValue for January as 20 and February as 40. Is this possible. The %complete changes on the task so I can't calculate January anymore. Is this a good time to store the value? Thanks for your help. Keith Nov 13 '05 #2

 P: n/a thanks Phil, I think I am going to try to store the % complete table each month. How is this to be done? Will I have a table for each month of the year... doesn't seem to be the best option for writing queries later. I am a bit confused on how this will look. Nov 13 '05 #3

 P: n/a You need a table with the Project details Projects ProjectID - Autonumber, ProjectName, Budget etc etc etc You need a MontlyTable DateID - AutoNumber, ProjectID - Long, PercentComplete - Decimal Set up a relationship between the 2 tables enforcing referential integrity between the 2 ProjectIDs You need a form based on Projects You need a subform based on MonthlyTable. Use a query sorted by ProjectID and date. The LinkChildFields & LinkMasterFields will again be the ProjectID You can also display on this subform the Budget * PercentCompleted and the total. By the time I have written this reply , I could have built the database. Have fun Phil "turtle" wrote in message news:11**********************@c13g2000cwb.googlegr oups.com... thanks Phil, I think I am going to try to store the % complete table each month. How is this to be done? Will I have a table for each month of the year... doesn't seem to be the best option for writing queries later. I am a bit confused on how this will look. Nov 13 '05 #4

 P: n/a Phil, Thanks for taking the time to write the reply... I will give it a shot Thanks so much. Nov 13 '05 #5

 P: n/a I will give this a try Phil but I don't understand how the data gets backed up each month. I don't see a date field that I can query to find out what my numbers are in February or March or etc... Thanks Nov 13 '05 #6

 P: n/a Whoops, sorry, forgot to include CompleteDate in the dates Table If you have the dates, there are functions to group stuff together. This will group by tear and month SELECT DatePart("yyyy",[CompleteDate]) AS [Year], monthname(DatePart("m",[CompleteDate])) AS [Month], Dates.CompleteDate FROM Dates GROUP BY DatePart("yyyy",[CompleteDate]), monthname(DatePart("m",[CompleteDate])), Dates.CompleteDate ORDER BY DatePart("yyyy",[CompleteDate]), monthname(DatePart("m",[CompleteDate])); "turtle" wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...I will give this a try Phil but I don't understand how the data gets backed up each month. I don't see a date field that I can query to find out what my numbers are in February or March or etc... Thanks Nov 13 '05 #7

 P: n/a I hate to ask more questions... but here goes What does the complete date represent? Stupid question I know.. but how do I take into account non complete entries. The percent complete might be 40% in February and 80% in March. So its not complete yet... I must be misunderstading how this all fits together. I am sorry for being such a pain... i am really trying to understand this. Nov 13 '05 #8

 P: n/a Why not just enter the date that the percentage is completed (probably near the end of the month) so that it will say 24 Dec 2004 25% 28 Jan 2005 53% 25 Feb 2005 74% etc. That assumes you enter the info on the last Friday (working day) of each month "turtle" wrote in message news:11**********************@c13g2000cwb.googlegr oups.com...I hate to ask more questions... but here goes What does the complete date represent? Stupid question I know.. but how do I take into account non complete entries. The percent complete might be 40% in February and 80% in March. So its not complete yet... I must be misunderstading how this all fits together. I am sorry for being such a pain... i am really trying to understand this. Nov 13 '05 #9

 P: n/a Basically we will enter 50% when we start a task and 100% when we finish the task. So in March the task could be 50% done but then in April it becomes 100% done. When i run my report I need to show in March that % complete was 50 and in April it was 100. I will then graph on those #'s. So when I change it in April I will still need March to show %complete as 50. Am I making any sense at all? keith Nov 13 '05 #10

 P: n/a Try SELECT ProjectID, DatePart("yyyy",[CompleteDate]) AS [Year], monthname(DatePart("m",[CompleteDate])) AS [Month], Dates.CompleteDate, PercentCompleted FROM Dates GROUP BY ProjectID, CompletedDate, DatePart("yyyy",[CompleteDate]), monthname(DatePart("m",[CompleteDate])), Dates.PercentCompleted ORDER BY ProjectID, CompleteDate; If the data is in correctly, you may have to mess around with this query a bit to get the right output. Without having the tables, it is a bit difficult to make sure it is right Phil "turtle" wrote in message news:11*********************@f14g2000cwb.googlegro ups.com... Basically we will enter 50% when we start a task and 100% when we finish the task. So in March the task could be 50% done but then in April it becomes 100% done. When i run my report I need to show in March that % complete was 50 and in April it was 100. I will then graph on those #'s. So when I change it in April I will still need March to show %complete as 50. Am I making any sense at all? keith Nov 13 '05 #11

 P: n/a Getting Close...but Feb would be a running total of Jan and Feb and so on. thanks Keith Nov 13 '05 #12

 P: n/a Hi Keith. Its bed time here Should be OK as long as you have Group by PercentCompleted, not Sum(PercentCompleted) Phil "turtle" wrote in message news:11**********************@f14g2000cwb.googlegr oups.com... Getting Close...but Feb would be a running total of Jan and Feb and so on. thanks Keith Nov 13 '05 #13

### This discussion thread is closed

Replies have been disabled for this discussion.