Elapsed Time

[Pickled Princess]

We are tracking downtime on our production equipment in an Access database.
The reports that are keyed into the database list the date of the downtime,
the equipment that was down and the length of time of each instance. We run a
query to isolate a certain piece of equipment with it’s downtime but then
would like for Access to be able to calculate the average elapsed time
between failures. Any suggestions would be greatly be appreciated by this wet
behind the ears Access user.

 

[Tom Ellison]

Dear Pickled:

The DateDiff function would likely be your key to doing this. Read up on
that first. It will calculate an interval in days, hours, minutes, etc.

If you have difficulty using this, post details of what you have for a more
specific response.

Are the start and stop date/time of each downtime recorded in a single row
of the table, or in two rows?

Tom Ellison

 

[Klatuu]

To do this correctly, you really need more than just the date of the down
time. You need date and time it went down and date and time is came back on
line.

 

[Pickled Princess]

It is just one date with each incident, no stop/start times.

 

[Tom Ellison]

Dear Pickled:

My mistake. So, use DateAdd to determine the ending time of an incident,
and calculate to the starting time of the next incident. Then average all
of those.

Not a simple query, however.

I will try to write something for you if you give full details: table
names, column names (only the necessary ones).

Tom Ellison

 

[John Vinson]

On Wed, 22 Mar 2006 12:38:27 -0800, Pickled Princess <Pickled

We are tracking downtime on our production equipment in an Access database.
The reports that are keyed into the database list the date of the downtime,
the equipment that was down and the length of time of each instance. We run a
query to isolate a certain piece of equipment with it’s downtime but then
would like for Access to be able to calculate the average elapsed time
between failures. Any suggestions would be greatly be appreciated by this wet
behind the ears Access user.

Ok, let's see if I understand.

On February 21 machine XYZ was down for 10 minutes.
On February 21 it was down again for 15.
On February 24 it was down for 45.

What's the time between failures?

You don't appear to have any information in your table which would
allow Access (or anything else) to answer the question, or perhaps I
don't understand the question.

John W. Vinson[MVP]

 

[Tom Ellison]

Dear John:

Why not? If your sample is the case (and I do not know that it is), then
you would know the machine was down for 70 minutes a total of 3 times out of
28 days (all of February, if that is the case).

(28 * 24 * 60 - 70) / 3

(days * hour per day * minutes per hour - down time) / number of failures

Seems sufficient to me! What am I missing?

Tom Ellison