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
Bytes IT Community
+ 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
Share on Google+
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" <ko****@vistacontrols.com> 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" <ko****@vistacontrols.com> 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" <ko****@vistacontrols.com> 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" <ko****@vistacontrols.com> 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" <ko****@vistacontrols.com> 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" <ko****@vistacontrols.com> 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.