• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

Microsoft access mean time to break query

Dear all, i am trying to create a mean time to break report in access, does anyone have any idea on how this would be created.  The fields i have are asset name and date

Thanks
0
dann47
Asked:
dann47
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
dann47,

What do you mean by "mean time to break report in Access"?

Please explain fully what you are trying to accomplish.

Thanks!
:)

JeffCoachman
0
 
dann47Author Commented:
Mean time break is the average amount of days between breakdown, so the calculation needs to be total days in between failure (taken from the date field) divided by the number of occurances.  So for example, if i had as below
Pencil 1   12/10/2007
Pencil 1    14/10/2007

I would get a mttb of 1 day
0
 
pmctrekCommented:
Your report needs to be just a list of assets and not include the break dates.  Then have a unbound text field using a query as its recordsource.  that query is an average of the difference of the dates.

The problem I can see is actually where to get the differences between the dates.

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
GeraldHlasgowCommented:
Can you be more specific about what the date represents?  Will every asset have at least one date entry even if it has never failed?
0
 
Jeffrey CoachmanMIS LiasonCommented:
dann47,

Yes, please provide some sample data.
0
 
dann47Author Commented:
No, each asset will only have an entry is there is a fail rate.  The date represents the last time there was an issue with said asset,  Sample as below

Pencil 1  29/10/2007
Pencil1   30/10/2007
Pencil 2  01/11/2007
Pencil 5   10/11/2007


0
 
GeraldHlasgowCommented:
I think you will find it close to impossible to calculate an accurate mean time to break report if that's all the data you have.

If the data only includes items which have broken, it will artificially shorten the mean time - in your example above pencils 3 and 4 haven't broken at all and pencils 2 and 5 have only broken once, so you can't calculate anything for these despite the fact that their data still contributes to the overall mean time between breaking.

The only item you could try to calculate a "mean time" for is Pencil 1. But even then, the real mean time between breaking isn't 1 day, unless the first Pencil 1 was originally put into use on 28/10/2007.  But if the first Pencil 1 was originally used in October 2006, the correct mean time would be around 6 months, not 1 day.
0
 
pmctrekCommented:
Gerald is right.. You need 2 additional fields per asset.

Date of origional purchse Date and then a failure interval.

interval[1] = failure_date[1] - puchase_date
interval[2] = failure_date[2] - failure_date[1]
etc

MTBF = AVG Interval

The interval column can be easily calculated using VBA either run peroidicly or from the report_load event.  This is done be creating a sorted by date in decending order and looping through the record set.
0
 
GeraldHlasgowCommented:
I suppose most simply all you need is one additional date which was the point at which the original asset was put into operation, and possibly, if you're looking at historical rather then solely current data, the date on which the asset was finally put out of operation.  Then the MTFB is the time the asset was in operation divided by the number of failures.  Assuming the asset is continually in operation, then the time in operation is either Time Now - Date First in Operation or, for a "retired" asset, Date Retired - Date First in Operation.

But as I say, even  this does make some assumptions, such as the fact that the asset is instantly replaced and never goes out of operation because of failure or indeed any other reason.

For example if something regularly failed every three months but it took a week each time to replace or repair it then the MTFB would actually be 12 weeks and not 13 weeks - because for one week out of each of those periods of 13 weeks it wasn't actually in operation.
0
 
dann47Author Commented:
The data will be entered the day that the breakdown occured, so therefore o pm;y need to work on the 2 or three entries that would be there.  So if we excluded anything with one break it would be more acceptable.  As for the installation date, impossible as some equipment been in use for longer than i have been alive.
0
 
GeraldHlasgowCommented:
If that will do then all you need to generate the information is a query a bit like the one below.

I've assumed the table is called Assets, the description in AssetName, the date in DateField, I'm sure you'll already have your own names to replace these with.

SELECT Assets.AssetName,
DateDiff("d",Min(Assets.DateField),Max(Assets.DateField)) AS TotalDays, Count(Assets.AssetName) AS Breakages, DateDiff("d",Min(Assets.DateField),Max(Assets.DateField))/Count(Assets.AssetName)    AS MeanTime
FROM Assets
GROUP BY Assets.AssetName
HAVING Count(Assets.AssetName)>2;

Open in new window

0
 
dann47Author Commented:
It works perfectly as the below code

SELECT IssueData.AssetDesc,
DateDiff("d",Min(IssueData.DateReceived),Max(IssueData.DateReceived)) AS TotalDays, Count(IssueData.AssetDesc) AS Breakages, DateDiff("d",Min(IssueData.DateReceived),Max(IssueData.DateReceived))/Count(IssueData.AssetDesc)    AS MeanTime
FROM IssueData
GROUP BY IssueData.AssetDesc
HAVING Count(IssueData.AssetDesc)>2;


Well done, first time i have had some code that has worked first time with only minor changes.

Kudos GeraldHlasgow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now