Solved

Microsoft access mean time to break query

Posted on 2007-11-20
12
717 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

622 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