Want to Forcast Project Completion

[Soulspike]

Hello all,

I am not very experianced in utilizing loops and recordsets but can not think of any other way to accomplish what i need.

I have a table shown here. I want to forcast out when the projects below will be complete by year complete. So basicly I need to take the average units/day and figure out what the total per year is and create a record for each project and year till the project is closed.

table1
Project	  Units/day	Total_Units
Project 1	4.29	     259
Project 2	1.94	     152
Project 3	3.19	      82
Project 4	2.30	      72
Project 5	0.16	      70


Table2
Project	  Year      Units_Forcasted
Project 1	2012	     100
Project 1	2013	     100
Project 1	2014	      59
Project 2	2012	     125
Project 2	2013	      27

Thank you for sharing your expertis

[zmbd]

Normally calculated results are not stored in a table unless needed for auditing or other historical uses.

SO, if I understand you correctly, you need to know in what year the project will be completed yes?

You fail to mention what the work week is... 7 days/week... 5 days/week... are Holidays inculded etc...

Basically.... build yourself a select query, determine the number of years you'll need to reach or exceed the production goal and then use the dateadd function to determine the year ending from the year started - and insert that as a calculated field in the query.

If you really need to have the expansion, then we can talk about Left joins and make table queries.

-z

[Rabbit]

Your projections seem way off. There seems to be some upper limit on production per year that you're not mentioning. Take project 1 for example, if you're making 4.29 units a day, it would only take 24 working days to hit your goal of 259 units. And yet you have that projected out until 2014. So either you're using some other calculation of which I'm unaware, or you have a hard cap of which I'm unaware.

Even if we assume that you mean 4.29 days a unit rather than 4.29 units a day, the projection calculation is wrong because if you assume a 7 day work week, that's only 85 units in a year.

[zmbd]

Rabbit,

I'M NOT CRAZY... YEA!!! I'm glad I'm not the only one that couldn't make sense of the two tables in OP for parts... that's why I gave just the general outline to the solution instead of trying to present the caculation as I just didn't have the time to put the old math degree to use on it!
:-)

-z

[Soulspike]

Sorry when i wrote out the tables i just used example data, i didnt calculate the actual times. What i wanted to show was that i need to see the total every year to the completion of a project. So I can calculate a cost per year for each project. The data is fake, i should have calculated the real stuff or mentioned that in the question.

Days/Week im using is 7

Additional information: I am talking about locomotive projects that i have a set starting value of units needing to be done and a run rate of how many my shops are doing a day. So i need to project completion over the future years to completion

[zmbd]

No time off for good behavior... :)
Easiest calc for the year's production is to take your units per day number mulitply it by 365 days per year so that you get units produced. (upd/dpy)
Adjust the 365 down for days not worked (holidays and the like).
Now divide the target productio by that number so that you have ((tgt)/(upd/dpy)) which will give you the number of years to complete the production. I guess the question here is if we should round the number up to the next whole year or not

Now we use the DateAdd function and the format to return the year in a calculated field.
Note here I'm not rounding and you'll have to replace my tokens:
Format(DateAdd( "y", ((tgt)/(upd/dpy)), startdate), "yyyy")

-z

[zmbd]

Soulspike:
I moved your new question to a new thead:

http://bytes.com/topic/access/answers/942762-want-forcast-number-projects-completed-per-year

-z