Tracking mulitple time variables per record (1 Viewer)

Hi All,

I have a database that tracks work flow. The flow is as follows:

1) employee receives work request
2) employee performs some tasks related to that request
3) employee needs additional information, and pauses work while wait for that info
4) employee receives additional information and resumes work on the original request
5) employee completes request


Right now my database tracks the total turn-around time that it took the employee to complete the request. It does this by using a function to count the business days between when the request was received and when it was completed. However, for the purpose of evaluating an employee's efficiency, I'd like to make sure that any time the work request is awaiting additional information gets subtracted out from the total completion time.

The other hitch is that the employee can perform some work, wait for more info, perform more work, wait again, perform work again, etc. So I need a methodology for "counting" the business days that the work request is waiting for info and then be able to save that data, and pick up counting again each time the empoyee puts it on hold.

Any ideas on how I might go about capturing this?

Simple Software Solutions

Hi

A suggestion is to create a boolean field in your table call IdleTime. So when you enter a given date/time you can get the user flag it as being waiting time. So when you perform your summation you can do it in two stages, once for True and once for False, knowing that both sums equal the total time but by subtracting the Idle time you can calculate the efficiency correctly.

CodeMaster:http://www.icraftlimited.co.uk

Hey DC,

Thanks for the idea. I considered having a some sort of a running timer, but my confusion results from my need (as directed by the boss) to only count full business days.

So I would somehow need Access to record the very first starting date. Then it would need to calculate how many business days passed before the status was changed to Waiting and save that number. Then it would need to record how many business days passed between that status change and the next status change back to Active. And then calculate biz days between that new Active status and completion, and add it to the first total.

Does that make sense? I think I confused myself writing it.

I have concerns over the granularity of your time tracking, but that is basically just a minor issue. The major issue is to design a database that helps you track what you want to know, based on the "Old Programmer's Rule" that says "Access won't tell you anything you didn't tell it first."

If you don't capture what you want to know then you won't be able to ask for that later.

Here is the trick. You can capture data either of two ways, conceptually.

You have a work request which has a starting date. But the starting date is only useful for sanity checks. Read up on database normalization if you have not previously done so. What you want is a parent/child table setup where you have employee tables, work order tables, and work action tables.

What you need is a work action record that shows each day on which the employee took action on the task. The mere presense of the entry means work was done on the task on that date. Add a STOPWORK field to show why someone had to stop working on that task on that day. You can add a reason code for the "STOPWORK" field to show that it was (a) a data hold, (b) a priority conflict hold, (c) a completion, (d) end of work day, or something else. (You can have many "something else" codes if you wish. Make it a combo box on the form you build for data entry.) Then, the days worked on something are just a Dcount.

Alternative: For each sequence of work actions, you have a record linked to the task number and employee showing a start date and stop date for the work action. Then you only have to analyze the start/stop dates. Make the rules such that you do a stop work on the day before a weekend or holiday and a restart on the day after.

Then it is a matter of grouping your data by work order number, sorting by date, and counting the days.

The_Doc_Man,

Thanks for your help. I hadn't considered tracking the work action in a different table. I think that makes a lot of sense, and I'm going to see if I can build that setup. I have a question about Access performing the calculations. There will not be an entry for each day a piece of work is outstanding, so I couldn't just count all the times "Work" appears. The only time an employee interacts with the database is when something new happens (new assignment, change in status of that assignment, completion of assignment).

So, still using your seperate table idea, am I correct in thinking that I would need something like:

Record1 1/2/2008 Start
Record1 1/5/2008 Wait
Record1 1/8/2008 Work
Record1 1/10/2008 Wait
Record1 1/11/2008 Work
Record1 1/13/2008 End

(for ease, lets just assume all these dates are business days)

And then I would say for Record1

-find Start and note the date;
-find the next time Record1 appears, note that date, and total the days between;
-find the next time Record1 appears - if the prior instance was "Wait", note the date but perform no calculation;
-find the next time Record1 appears, note the date, total the days between, and add that to the first amount of days between
**repeat previous two steps until next instance of record = "End"**

And then my running total would be the total amount of days that passed when work was actually being performed.

Does that make sense, and is it anywhere close to accurate?

Thanks,
CF

**P.S. On a side note, its coincidental that when I checked on this thread there was a reply from you, because I had been reading your exchange with Banana on Null theory and trying my best to follow along. I'm extremely impressed with your knowledge of the subject. The members of this forum already have me in awe as I am a finance person who somehow got tapped to build a database, despite my having no prior Access or VBA experience. I've learned just about everything I know about databases from this forum, and I really appreciate you and everyone else taking the time to assist me, and share your extensive expertise. Thank you.

The biggest problem I see is this: You must decide what the entry means before you build it.

Let's consider this structure for the table:

* WorkOrderNo, foreign key to table of authorized work order numbers
* WorkDate, date
* WorkEmpl, foreign key to employee who worked on it (optional, perhaps)
* WorkCode, short integer (WORD) code for type of event, foreign key to a code explanation table for lookups and spelling things out on reports

OK, to figure this out as to work info... build a query that does a GROUP BY on the WorkOrderNo and an ORDER BY on WorkDate. Let's simplify to show that once the assignment has been made, WorkEmpl is constant.

Read up on Recordset operations. Open the QUERY in code. Perhaps open an output recordset to give you the times you want in records for later reporting.

In the code module, start the recordset at the beginning (recordset.MoveFirst). Write the code to test the WorkOrderNo to be the same as the previous. If not, it is time to write out the summary record and start a new summary record. Obviously, there is a special-case check for the first record.

The summary record will have TWO times - work time and wait time.

OK, read the record. Sanity check it, because if it is the first record for the work order number, it MUST be a start. If not, issue an error record to a third table (perhaps) and keep going. OK, let's say it is well behaved. You have a Boolean variable that is of course True / False. Have your logic use it to decide if the work order state is currently Working or Waiting. (You choose which way is true and which is false.) When you see a record, do TWO things: Record the time of the record and set the Boolean.

Example: It is a START record. Set the Boolean to your WORKING state and record the date/time in a date variable local to the code.

Now read another record (.MoveNext). It is a WAIT record. You are in the WORKING state. So take the difference between the stored time and the current time, then add that to the WorkTime counter. Switch to non-working state. Store the current time.

OK, next record is a RESUME record. You were non-work state. Compute the time since the last event and add that to the WAIT time counter. Switch states, store the time.

You'll step through, flipping and flopping the Boolean and adding time to one or the other of the counters. Eventually you will hit a work order change. Write out the current stored work time and wait time for that work order number. Now reset the totals for the work time and wait time, set the correct state for your Boolean, store the start time.

Fine points: At each new record, you can test whether the current record is valid given the previous state of the Boolean. I.e. if you see two WAIT records in a row, you might guess that someone didn't store a RESUME record. If you see any work order for which the last thing isn't the CLOSEOUT code, you have a work order in progress.

You can group the query according to the employee also. That way, if you are tracking employees you group one way. If you are tracking work orders you group the other way.

Myself, I would have done it with times as well as dates, and have the rule be that you store a START time and a STOP time in the same record along with the STOP code. Then the work time is the difference between the start and stop times and the wait time is the difference between the current start time and the previous stop time. (Again, do this via code because of the insanity involved in doing this via queries only.)

Ok, that makes a lot of sense. I'll see what I can come up with, and check back in if I need some clarification.

Thank you very much for your help.