Solved

Microsoft access mean time to break query

Posted on 2007-11-20
12
680 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sub Reports 8 23
Omit Values from a Combo Box in access 2 13
Access 2003, sort table via code 5 9
tabctrl with page click event 9 13
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now