Sum creating "Accumulative value"

[DerickD]

Hi All,

Thanks for taking the time....

I have a table that contains two coulombs.

1) Proccess_Step
2) Expected_Time

Proccess_Step is a single step in a life of a file :
Step1 Send file to Manager
Step2 Print
Step3 Mailing
Step4 Questionnaires Returned
Step5 Data entry
..etc...etc..
Step20 Confirmation sent to Manager.

Expected_Time is the amount of days that is allocated to each step :
Step1 0 day
Step2 0 day
Step3 1 day
Step4 5 day
Step5 2 day
....etc...etc...
Step20 1 day

What I would like to do is make a calculation that will take the date when Step1 has been started, and add to that each of the 'Expected_Time' for each of the steps.

This would then produce a forecast that can say, at what step we should be at on what day. So then we can easily see if we are behind, or ahead of schedule in the life cycle of the file.

All I would need is to find a way to make a query that would just add the days together in the correct order. So I would have a query that would produce the following.

Step1 0
Step2 0
Step3 1
Step4 6
Step5 8
....etc...etc...
Step20 120

Once I have the "Accumulative value" for each step then I can do the rest no problem...

Thanks alot for reading all this, and hope that is makes
sense.

Derick.

 

[dodge20]

Check out this FAQ

faq701-1155

Dodge20

 

[SgtJarrow]

While that may provide an incremental number for the lines, I don't think that will solve the problem. What Derick seems to be asking for is the combined time of production at each step....

This will require a bit of work on your part depending on your database setup. Do you only track one file at a time? Do you want numbers of days, or how about showing expected dates? Do you have to accommodate for weekends and holidays not being included in the day calculations?

While this task can be accomplished, its difficulty will be proportional to your VBA coding skills, database design, and conceptual views...

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Golom]

You can build an SQL query that computes running totals (or dates in this case) with something like

SELECT A.[Step_Number], A.Description,
(Select Sum(B.Days) From Expected_Time As B
Where B.[Step_Number] <= A.[Step_Number]) AS TotalDays,
(Select DateAdd(&quot;d&quot;,Sum(B.Days),Date()) From Expected_Time As B
Where B.[Step_Number] <= A.[Step_Number]) AS EndDate
FROM Process_Step AS A
ORDER BY A.Step_Number;

I've taken some liberties with your data structure as follows:

* Changed the &quot;Step_Number&quot; field to a numeric so that the &quot;<=&quot; comparison will work. In your example &quot;Step10&quot; will sort before &quot;Step2&quot;.
* Used &quot;Date()&quot; (i.e. the current day) as the calculation base.
* Displayed both the number of days and the end date in the query.