Solved

Microsoft access mean time to break query

Posted on 2007-11-20
12
710 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

732 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