Process Tracking

[ChrisKONE]

I am not exactly new to Access, but I can't say I do the most
complicated things with it. I use the query design window, rather than
SQL, about 99.9% of the time.

I'm stuck on a problem, and I saw a similar question from a while back,
but it didn't go quite far enough.

Here was the original post:
*****************
I have a table of steps, tracking the progress of a process. Some of
the
steps are time-sensitive to the completion date of earlier steps. For
example, if Step1 was completed on June 15, then Step2 is due on June
20 and
Step3 is due on June 25.

So, in its simplest form, I'm thinking my table would look like:

tblSteps
*fldStepNumber
fldStepDescription
fldCompletionDate
fldDueDate


Record one would be Step1, and so on.


How can I make fldDueDate of Step2 = [fldCompletionDate +5] of Step1,
and
fldDueDate of Step3 = [fldCompletionDate +10] of Step1?


Also, I've read that one doesn't store calculated values in tables, so
then
would this be done in a query or in a form?


Thanks in advance.


Anxiously wondering how,

Response:
Hello Mathew.
You can create a query to update the fldDueDate field. First, qreate
the
query qryNextDueDates:
SELECT [fldStepID]+1 AS NextStepID, [fldCompletionDate]+5 AS
NextDueDate
FROM tblSteps;

Then create the update query:
UPDATE tblSteps INNER JOIN qryNextDueDatesON tblSteps.fldStepID =
qryNextDueDates.NextStepID SET tblSteps.fldDueDate =
[qryNextDueDates].[NextDueDate];


I hode, this helped.
Regards,
Wolfgang
***********

My problem is this...

the original post was trying to make a table with a set number of steps
(evidently a single process).
I am trying to set up a contract tracking database where one table
holds a unique project number, with a dependent table holds a list of
standard action items repeated for each project number. (each of these
records does have an autonumber assigned to it for key purposes) there
is a third table where I have the standard actions listed with number
of days before due date.
So the first table would have recordset like this:

FLNumber Bid Date
600000 01/11/06
600001 05/11/06

and the second table would look something like this:

FL Number Action Due Date(initially blank)
Actual Date
600000 Step 1 01/01/06 (uses Bid date from first field
as starting point)
600000 Step 2 (previous steps' due date, plus the
number of days from the actions table - until the Actual Date is filled
in, then it would read from that date )
600001 Step 1 05/01/06
600001 Step 2 Same as first FL number above

the third table looks something like this:
Step DaystoDue
Step 1 10 (before bid date)
Step 2 5 (after first due date, or actual date if it is
filled in)

The durations between the steps would be the same (at least during
setup), but the first due date would be different for each FL number.
I also need to have the ability to manually change the due date that
doesn't get wiped out if it happens to be more or less than the
"standard" duration. so I assume I need an update query on the second
table that would only update empty due dates and/or when the actual
date is updated change subsequent due dates.


Although I don't typically use SQL, I assume I could copy/paste into
the SQL mode and it would show me the "visual" view I'm used to.

Any help would be appreciated...

 

[Tim Ferguson]

Record one would be Step1, and so on.


How can I make fldDueDate of Step2 = [fldCompletionDate +5] of Step1,
and
fldDueDate of Step3 = [fldCompletionDate +10] of Step1?

You don't. This really looks like a Big Design Problem and probably needs
a Lot More Analysis. The idea of putting all the "steps" into one table
is fine as long as the steps are all the same things; but these
dependencies indicate that they really aren't the same and probably
shouldn't be in the same table. Remember 3NF means that a value must be
dependent on the PK of the record and on nothing else -- in this design
you don't just have dependencies going across non-key fields, you have
them spreading into other records. This just is not R and you are not
going to be able to model the thing like this at all, I'm afraid.

If you post more details, we may be able to help out a bit; but I suspect
you have a non-trivial business need!

All the best

Tim F

 

[Jeff Boyce]

Let's see if I have this straight...

You have a project. The project has a StartDate.

The project goes through several steps. Each step for the project has a
StartDate.

There's a fixed list of known steps.

Each step has a "to be completed within xx days of" value, as well as a
"step-that-precedes" value.

You want to be able to calculate "DueDate" values.

A couple more questions, to go with Tim's comments...

Are the steps mutually exclusive? That is, is each step independent (no
overlap) of the other steps?

Are the steps sequential, or can they be concurrent? That is, is only one
step at a time "in effect", or can several steps be running in parallel?

If this really is a "project-oriented" task, is there a reason you aren't
using a project management software?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not exactly new to Access, but I can't say I do the most
complicated things with it. I use the query design window, rather than
SQL, about 99.9% of the time.

I'm stuck on a problem, and I saw a similar question from a while back,
but it didn't go quite far enough.

Here was the original post:
*****************
I have a table of steps, tracking the progress of a process. Some of
the
steps are time-sensitive to the completion date of earlier steps. For
example, if Step1 was completed on June 15, then Step2 is due on June
20 and
Step3 is due on June 25.

So, in its simplest form, I'm thinking my table would look like:

tblSteps
*fldStepNumber
fldStepDescription
fldCompletionDate
fldDueDate


Record one would be Step1, and so on.


How can I make fldDueDate of Step2 = [fldCompletionDate +5] of Step1,
and
fldDueDate of Step3 = [fldCompletionDate +10] of Step1?


Also, I've read that one doesn't store calculated values in tables, so
then
would this be done in a query or in a form?


Thanks in advance.


Anxiously wondering how,

Response:
Hello Mathew.
You can create a query to update the fldDueDate field. First, qreate
the
query qryNextDueDates:
SELECT [fldStepID]+1 AS NextStepID, [fldCompletionDate]+5 AS
NextDueDate
FROM tblSteps;

Then create the update query:
UPDATE tblSteps INNER JOIN qryNextDueDatesON tblSteps.fldStepID =
qryNextDueDates.NextStepID SET tblSteps.fldDueDate =
[qryNextDueDates].[NextDueDate];


I hode, this helped.
Regards,
Wolfgang
***********

My problem is this...

the original post was trying to make a table with a set number of steps
(evidently a single process).
I am trying to set up a contract tracking database where one table
holds a unique project number, with a dependent table holds a list of
standard action items repeated for each project number. (each of these
records does have an autonumber assigned to it for key purposes) there
is a third table where I have the standard actions listed with number
of days before due date.
So the first table would have recordset like this:

FLNumber Bid Date
600000 01/11/06
600001 05/11/06

and the second table would look something like this:

FL Number Action Due Date(initially blank)
Actual Date
600000 Step 1 01/01/06 (uses Bid date from first field
as starting point)
600000 Step 2 (previous steps' due date, plus the
number of days from the actions table - until the Actual Date is filled
in, then it would read from that date )
600001 Step 1 05/01/06
600001 Step 2 Same as first FL number above

the third table looks something like this:
Step DaystoDue
Step 1 10 (before bid date)
Step 2 5 (after first due date, or actual date if it is
filled in)

The durations between the steps would be the same (at least during
setup), but the first due date would be different for each FL number.
I also need to have the ability to manually change the due date that
doesn't get wiped out if it happens to be more or less than the
"standard" duration. so I assume I need an update query on the second
table that would only update empty due dates and/or when the actual
date is updated change subsequent due dates.


Although I don't typically use SQL, I assume I could copy/paste into
the SQL mode and it would show me the "visual" view I'm used to.

Any help would be appreciated...