MTBF

[kristi1023]

Has anyone ever written a VB or VBA module that will calculate the mean time between failure for a unit or know of a Web site where I may download such a calculation?

Any insight is greatly appreciated, as I'd rather not have to think so hard on a Monday morning! Thank you!

Kristi

 

[BlackKnight]

Hi,
This might help...although I always thought "mean" was the average unless you meant median?

Private Function CalcMeanFailureTime(ByVal UnitID As Long) As Double

Dim rs As New Recordset
Dim strSQL As String, AUnit As String
Dim Result As Double

Result = -1
strSQL = "SELECT tblUnit.UnitID, tblUnit.UnitName, Avg(tblFailure.FailureTime) AS FailTime " & _
"FROM tblFailure INNER JOIN tblUnit ON tblFailure.UnitID = tblUnit.UnitID " & _
"GROUP BY tblUnit.UnitID, tblUnit.UnitName HAVING tblUnit.UnitID=" & NZ(UnitID, 0)
With rs
.Open strSQL, CurrentProject.Connection
If .EOF And .BOF Then
MsgBox "This unit has not failed."
Else
Result = !FailTime
End If
.Close
End With
Set rs = Nothing
CalcMeanFailureTime = Result
End Function

Have a good one!
BK

 

[kristi1023]

Thank you, BK, this will give me a good starting point. I have many other components to consider, such as number of months in the field and ETI (elapsed time indicator) readings. My final calculation will be composed of the sum of ETI divided by the number of months in the field. Thank you for your time in answering my post.

Again, any further insight is much appreciated!

Kristi

 

[BlackKnight]

Hi,

How are your tables set up? I am assuming this for the query.

tblUnit
-------
UnitID
UnitName
MonthsInField

tblFailure
----------
UnitID
ETIReading 'I assume you may have multiple
'readings per unit?

so a query like:

SELECT tblUnit.UnitID, tblUnit.UnitName, Sum(tblFailure.ETIReading) AS TotETI, tblUnit.NumMonths, [TotETI]/[NumMonths] AS FailTime
FROM tblFailure INNER JOIN tblUnit ON tblFailure.UnitID = tblUnit.UnitID
GROUP BY tblUnit.UnitID, tblUnit.UnitName, tblUnit.NumMonths
HAVING (((tblUnit.UnitID)=[Unit]));

Have a good one!
BK