Tom, I had to change thinking a little bit, based upon
what the client is looking for. I hope that this helps.
I've set up a sample small database, to include the
fields to be computed:
DateCreated DateClosed MTBF MTTR
10/7/99 10:24 AM 10/14/99 10:39 AM 168.25
10/14/99 1:44 PM 10/14/99 1:47 PM 0.05
10/15/99 7:06 AM 10/16/99 6:30 AM 23.40
10/16/99 7:14 AM 10/16/99 7:39 AM 0.43
10/17/99 6:37 AM 10/18/99 8:18 AM 25.68
The MTTR values were generated through the following SQL
(via Design view):
SELECT [TBL-AssetRecord].DateCreated, [TBL-
AssetRecord].DateClosed,
Datediff("n",[DateCreated],[DateClosed]/60 AS MTTR
FROM [TBL-AssetRecord]
GROUP BY [TBL-AssetRecord].DateCreated,[TBL-
AssetRecord].DateClosed
ORDER BY [TBL-AssetRecord].DateCreated
Was the above SQL query what you were asking for?
You're correct, and thank you, using minutes and dividing
by 60, will give x.xx in hours and hundredth hours (now,
I have to get it to 2 decimal places, but I think I can
figure that out). I did get MTTR to work on my new query.
For MTBF, the query is not comparing all of the
DateCreated or DateClosed fields, but actually taking one
column of data, subtracting DateClosed from DateCreated
to produce Mean Time To Repair (located on same line in
query). On the same query, was to be MTBF, however,
using record #2's DateCreated and subtracting record #1's
DateClosed, and so on. Therefore, Record #1 would not
have MTBF at all (based upon first failure at start of
day). Record #2's MTBF (in this example) would be 3
hours and 5 minutes, or 3.083 (repeating) hours.
Also, there will not be a time when DateCreated for a new
incident is earlier than the previous incident's
DateClosed...therefore, no negative times.
Am I heading in the right direction?
CALVIN
-----Original Message-----
Dear Calvin:
You said:
I have to set up Date Closed into another value, to be used later in
computation with the next Date Opened to produce MTBF (Mean Time
Between Failures)
I'm not sure what you mean, but that doesn't sound like what I tried
to express.
Depending on whether you can uniquely define which is the "previous
record" determines by the data it contains, you don't have to do
anything extraordinary. I can provide you with the query to do this.
Just post a query that returns everything you need other than the
value from the previous record. Make sure it is ordered uniquely so
that the row from which you want to extract the Date Closed is the one
just before the one where you have the Date Opened. I'll add the Date
Closed value to the query, then you can get to work calculating on it.
Thanks, Tom. This is my first time in a Newsgroup such
as this...I'm also getting into a time conflict, because
I want to get this problem solved soon for my client. I
apologize for any inconvenience. I also have to find a
machine that has Access on it to be able to test, and
that also has a time limitation (like 5pm CT).
Anyway, I have been thinking about what you've said to
this point. It sounds as though I have to set up Date
Closed into another value, to be used later in
computation with the next Date Opened to produce MTBF
(Mean Time Between Failures). I have to brush up on my
SQL, too.
Let me get back with you, so that I can work on testing
and SQL. More later.
-----Original Message-----
Dear Calvin:
If you feel there are things we cannot accomplish in
the
newsgroup,
you may email me first. Please explain why you feel
we
need to speak
by phone. I can then reply with my phone if you have convinced me.
So you understand, most of us answering questions here are volunteers
who try to make a living as well. In the newsgroup,
we
can answer
questions as time permits, or ignore the questions
when
we're to busy.
So, we can get a tiny bit touchy about making deeper commitments.
I personally try to spread my limited time so I can
help
the largest
number of people. The newsgroups are the best venue
for
that, so
stick it out in here if you can.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Wed, 24 Mar 2004 10:44:24 -0800, "Calvin Shane"
Thanks, Tom...Is there a phone number to reach you? I
know Access, but don't know SQL as well, but I'll
give
it
my best shot.
CALVIN
-----Original Message-----
Dear Calvin:
The problem of getting the value from the previous
record is a
separate one from what I addressed. To retrieve the
previous record
would require a correlated subquery.
In order to be able to do this, you must uniquely define
how to
retrieve the previous record, based on the data it
contains. By
uniquely I mean that, given your definition, there can
be at most one
row that meets that definition.
Let's work first on a query that can retrieve and
display this value
from the previous row. The rest is easy.
Please provide the SQL for a query that provides
everything except
this previous Date Closed. This query should be sorted
so the rows
are in the order that defines which is the previous row.
I'll try to take if from there and show how this can be
done.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 22:57:57 -0800, "Calvin"
Thank you...My question is how to get the current
record's Date Opened, minus the previous record's Date
Closed?
"Datediff(excuse the syntax)[DateOpened] -
([DateClosed]-
1)"
to denote the previous record. The result goes on the
line of the current record.
Does this involve having to use Visual Basic, or SQL?
Also, this is a query, that will eventually be used in
a
report (summing the MTBF's and averaging them).
CALVIN
-----Original Message-----
Dear Calvin:
How about using Datediff to get the difference in
minutes, and divide
by 60, or in seconds and divide by 3600? Then you'd
have hours with a
decimal point. Taking the integer and fraction parts
of
this, you'd
have hours and fractions of an hour. Multiply the
fraction by 60 and
get minutes, with decimals again.
Should not be hard to do.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 22:08:03 -0800, "Calvin Shane"
My client is using Access to provide dates and times
on
equipment failures (using Date and Time opened as one
field, and date and time closed as one field (date
and
time separated by a space, in the following format:
Date closed field is mm/dd/yyyy hh:mm AM/PM
Date opened field is same as above.
Instead of using Excel (currently), he wants Access
to
produce Mean Time Between Failures (MTBF), which is
the
date/time closed of the next record, minus date/time
opened of the previous record (record -1), and
produce
result in the line of previous record, and so on.
MTBF
will not be produced for the 1st record, but will
thereafter. I've learned about using "Datediff", but
currently have no PC with Access to test it. With
these
formats, is it possible using "Datediff" to
produce
a
result in hours/minutes or hours.hundredth hours
(like
1.2 hours)?
.
.
.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
