How to track transit time for each step of workflow process?

[M.]

Hi,

This question regards a problem that I ran into while designing a new Access
database for my company.

Question
Which solution works best for the problem defined below= Two solutions are
suggested, alternative solutions are welcomed. What are the advantages of
(not) using a linked list approach?

Situation
Our customers want to place an order by sending a document with the
necessary specifications of the order. Before execution of this order, it
needs to be checked and approved by several people. Sometimes, the document
is even sent back to the customer for revision if parts of the information
are unclear or missing for approval.

Required information
We want to keep track of the duration of the whole process from placement of
the order until final approval of the order. Therefore we want a monthly
overview of the average time that each stakeholder in the workflow requires
to check and approve his/her part.

Example of required information report

March 2008
50 orders received

Average approval time in days
Secretary 5
Operational manager 10
Finance 7
Management 2
Customer 3

Suggested Table design

Table Orders
Order_ID (PK)
Customer_ID (Ref Key to Customers table)
Etc

Table Groups (contains customer, secretary, operational manager, finance,
etc.)
Group_ID (PK)
GroupName

Table Status (contains approved, revision, questions, etc.)
Status_ID
StatusName

Table Orders_Approval_Tracking
Order_ID (ref key to orders table)
TrackingNr (starts from 0,1,2,3 etc. voor each Order_ID approval tracking
record)
Group_ID
Date
Status_ID
Etc.

In this way, I could design a query that joins sequencal records (= approval
steps) based on difference in date if I could secure that records sorted by
date receive constant increasing numbers like 0,1,2,3, etc..

Example
record 1 (order_ID =1; TrackingNr = 0) joined with
record 2 (order_ID =1; TrackingNr = 1)
The group defined in record 1 took ** days to finish approval


Alternative would be to use a linked list, like this:

Table Orders_Approval_Tracking
OAT_ID (Primary Key)
Order_ID
Previous_OAT_ID (referencing to previous tracking record for same order_ID)
Group_ID
Date
Status_ID
Etc.

In this way, I could design a query that JOINS each approval tracking record
R1(except the first one) with its previous record R2 ON
R1.Order_ID=R2.Order_ID AND R1.Previous_OAT_ID = R2.OAT_ID and again
calculate the difference in date as approval time Requirement.

Example
record 1 (order_ID =1; Previous_OAT_ID = 111) joined with
record 2 (order_ID =1; OAT_ID = 111)
The group defined in record 1 took ** days to finish approval

Question (repeated)
Which solution works best for the problem defined below= Two solutions are
suggested, alternative solutions are welcomed. What are the advantages of
(not) using a linked list approach?


I hope anybody has experience with these kind of workflow information needs
and can give me useful feedback on the best approach to tackle this problem.

Best regards and many thanks,

M.

 

[Evi]

What about using the first design but adding an Enddate field to
TableApprovementTracking and calling the first field StartDate (don't call
it Date, that's a Reserved word) so that you can record when the order
arrives at and when it leaves a group.
If the item goes back to the group later, that's OK, you can still add up
the total days of involvement. Or what about letting the StartDate and
EndDate record both date and time in case the item goes from one group to
another within a day and then calculate the hours it spends with each group,
rather than the days. You can always round the total down to days when you
calculate.

Efi