Solved

Microsoft access mean time to break query

Posted on 2007-11-20
12
692 Views
Last Modified: 2008-02-01
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
Comment
Question by:dann47
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20318338
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
 
LVL 7

Author Comment

by:dann47
ID: 20318362
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
 
LVL 3

Expert Comment

by:pmctrek
ID: 20318684
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20318998
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20319879
dann47,

Yes, please provide some sample data.
0
 
LVL 7

Author Comment

by:dann47
ID: 20320178
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
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20320302
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
 
LVL 3

Expert Comment

by:pmctrek
ID: 20320564
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
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20320661
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
 
LVL 7

Author Comment

by:dann47
ID: 20326243
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
 
LVL 1

Accepted Solution

by:
GeraldHlasgow earned 500 total points
ID: 20326320
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
 
LVL 7

Author Comment

by:dann47
ID: 20334164
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
SQL Group on First occurrence 9 27
Trying to force an answer in a combobox 7 18
Modify report 8 13
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question