Solved

How to use the datediff function to compare dates for each filtered row in a recordset that may or may matche the same exact criteria in the previous row.

Posted on 2006-06-09
15
613 Views
Last Modified: 2012-06-27
Existing Senerio
************
I use an Access query to filter out each part that I would like to anlyze the failure rate on using where = ac12345 (See Figure 1).  When the query is complete I export the data to an excel spreadsheet where I spend lots of hours having fun.  For each part I use a formula to calculate the datediff between the system_install_date and the RepairStartDate to get the failure rate value.  

My Results are:
1. Part ac12345 failed: 5 times
2. Part ac12345 Railure rate is: 12456 (add up the value returned from the date diff function for each row)
3. Part Mean time between failure is:  4 (Part failure # devided by Failure rate number)
4. Part Repair Hour Total is: 45

Problem
******
1. I will never see day light doing it this way :)
2. How can the recordset below be modified to do this automatically to perform the manual process and send the output to a table instead of the immediate window.

Dificulity
******


1. If the row that is being analyzed does not have one or more matching rows for a specific autoid then the ddatediff is just between the Part_Install_Date and RepairStartDate and the record resumes going through the rest of the record set until EOF.

                                                                                         OR

1.1  If one or more rows have the same autoid (current record) then the next datediff will be between the RepairClose Date from the previous matching row to the next (current) RepairStartDate on the next row.  If there are one or more matching rows for a specific autoid the the record resumes going through the rest of the record set.  

                                                                                            OR

1.2 If the row that is being analyzed has a  PartChangeNumber with a number (1,2,3 or 4 - No letters) and the date then use the NewPartinstallDate
to perform the datediff between the NewPartinstallDate and RepairStartDate.  If one or more rows have the same autoid (current record) then the next datediff will be between the RepairClose Date from the previous matching row to the next (current) RepairStartDate on the next row.  If there are no more matching rows for a specific autoid the the record resumes going through the rest of the record set.  

2. If a row has a blank date field then the datediff is not performed (record is not counted/skiped) and move to the next row because the repair job may not be finished at the time I ran the query or Human Error.


Figure 1
*******
 PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate
ac12345     12345         1/1/94                     1            6          5/6/98                 5/8/98                         3                     (blank)  
ac12345     12345         1/1/94                     2            5          7/1/99                 7/21/99                       3                     (blank)
ac12345     12345         1/1/94                     3            2          5/3/01                 (blank)                        3                     (blank)
ac12345     99999         5/1/94                     5            3          6/2/00                 7/1/00                         3                      (blank)
ac12345     88888         8/1/95                     4            2          9/1/05                 9/2/05                         3                     (blank)
ac44444     22222         1/1/98                     4            4          3/5/00                 3/7/00                      (blank)                (blank)
ac55555     33333         5/6/99                     6            3          6/7/01                 6/8/01                         4                     (blank)
ac55555     44444         5/6/99                     5            0          4/1/05                 4/3/05                         4                     (blank)
ac55555     12345         5/6/99                     4            2          3/1/03                 3/1/03                         4                    (blank)    
ac12345R1 12345         (blank)                     2            3          5/1/06                 5/2/06                         3                    1/3/04                    
ac12345R1 99999         (blank)                     1            4          6/1/06                 6/2/06                         3                    3/1/05  

Existing:
******
Public Function DaoRst() As Integer
Dim daoDB As Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim t As TableDef
Dim rst2 As DAO.Recordset                'Question I'm thinking I will have to create another recordset that will include all fields and base it off of rst1?

Set daoDB = CurrentDb
Set t = daoDB.TableDefs!tblRepair         'Absolute Table Reference.
Set rst = t.OpenRecordset(dbOpenSnapshot)

s = Forms!Form1!cboPart                     'Reads-in part selected from From1.
If Len(Trim(s)) = 0 Then Exit Function

'Filter the Selected record
rst.Filter = "part='" & s & "'"
Set rst1 = rst.OpenRecordset                'New recordset based on selected Part

'Check if the selected Part has failure record(s) listed in tblJunction.
If rst1.RecordCount = 0 Then
    MsgBox "Part Not Found.  Please Make Another Selection"
    Else
    If rst1.RecordCount > 0 Then rst1.MoveLast
End If

0
Comment
Question by:cesemj
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 100 total points
ID: 16874051
Most can be done with queries and a query can be exported to an excelsheet or changed into a maketable query to store the results.

Your Results with a query:
1. Part ac12345 failed: 5 times
select count(*) as Failed from tblParts where [PART#] = 'ac12345';
2. Part ac12345 Railure rate is: 12456 (add up the value returned from the date diff function for each row)
select [PART#], sum([RepairClose Date] - [RepairStartDate]) as FailureRate from tblParts where [PART#] = 'ac12345' Group by [PART#];
(Leaving out the WHERE will give it for all parts)
3. Part Mean time between failure is:  4 (Part failure # devided by Failure rate number)
select (select count(*) as Failed from tblParts where [PART#] = 'ac12345')/(select [PART#], sum([RepairClose Date] - [RepairStartDate]) as FailureRate from tblParts where [PART#] = 'ac12345' Group by [PART#]) as MTBF from tblParts;
4. Part Repair Hour Total is: 45
select [PART#], sum([Time 1] + [Time 2]) as RepairHours from tblParts where [PART#] = 'ac12345' Group by [PART#];

Clear ?

Nic;o)
0
 

Author Comment

by:cesemj
ID: 16874449
Hello and thank you,

If I created an make=table query for number two, I will still have to go through the table and remove the values for the same AUTOID that appears more than once in the filter set because the the recordset will do the following:

1. Read the first row and perform the date diff between Part_install_date and RepairStartDate and move on to the next record and do the same until the EOF.  No problem, but if another Row and/or Row2 and Row3 AUTOID match the same AUTOID and PART# from Row1 then I must do the date diff between the ROW1 RepairClose Date  - ROW2 RepairStartDate and move to the next row (ROW3) and do the date diff between the ROW2 RepairClose Date  - ROW3 RepairStartDate.  

Row 1:
******
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate
ac12345     12345         1/1/94                     1            6          5/6/98                 5/8/98                         3                     (blank)  

Row2:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate
ac12345     12345         1/1/94                     2            5          7/1/99                 7/21/99                       3                     (blank)

Row3:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate
ac12345     12345         1/1/94                     3            2          5/3/01                 (blank)                        3                     (blank)


That is my problem when I do it manually and when I use a query to do it.  Tell me what you think?

Thank you agiain in advance for your insight.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16874555
Hmm, isn't the needed figure the same as the entire period (Part_Install_Date -/- Row3.RepairStartDate) minus the datediff of the [RepairStartDate] till [RepairClose Date] periods of row 1 and row 2 ?

For that an additional Group by query can be used to get the Max(RepairStartDate) for the combination of the [PART#] and AUTOID

Will have to be off to bed now so my next response can take a while :-)

Nic;o)
0
 

Author Comment

by:cesemj
ID: 16875079
Thanks I'll keep working on it.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
ID: 16878003
This will put the previous RepairClose Date on the same line:

SELECT a.*, (SELECT Max(b.[RepairClose Date]) from MyTable AS b WHERE b.[RepairClose Date] < a.RepairStartDate AND b.[Part #] = a.[Part #] AND b.AutoID = a.AutoID) as PrevRepairCloseDate FROM MyTable AS a
ORDER BY [Part #], AutoID;

Now you can do datediff to extract the servicieable time to calculate MTBF, etc.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16879498
Hello cesemj

Based on Figure1, the first query would be (incorporating Ray's idea above) this. Let's call it Figure2.

SELECT
    Figure1.*,
    CVDate(Nz(Part_Install_Date,NewPartInstallDate)) AS InstallDate,
    (   Select Top 1 RepairCloseDate
        From Figure1 As TMP
        Where [Part#] = Figure1.[Part#]
            And AUTOID = Figure1.AUTOID
            And RepairCloseDate<Figure1.RepairStartDate
        Order by RepairCloseDate Desc
    ) AS LastRepairDate,
    RepairStartDate-CVDate(Nz(LastRepairDate,InstallDate)) AS DaysToFailure
FROM Figure1;

This gives you the days to failure for each row. You can now apply a total query to this one, called quite obviously Figure3:

SELECT
    Figure2.[PART#],
    Count(*) AS FailedNb,
    Avg(Figure2.DaysToFailure) AS MeanDays,
    Sum(Time1)+Sum(Time2) AS TotalHours
FROM Figure2
GROUP BY [PART#];

I did not quite understand your formulas for "failure rate" (it seems to be a sum) or for "part mean time between failure" (how do you get the result 4?). Anyway, besides the count of failures, none of your calculations seem correct. Did you even apply your own "DateDiff" rules when calculating the sample results?

Anyway, this should give you enough to get started.

(°v°)
0
 

Author Comment

by:cesemj
ID: 16881718
Hello and again thank you all for your guidance and insight,

Mr. harfang, GRayL, nico5038 and everyone who has taken time out of your schedules to assists me I am SORRY for not inserting the correct values for sample calculations.  I have no excuse and should have checked the information I pasted from my excel spreadsheet.

FR = Failure Rate 1: Failure rate is the value returned form the datadiff calculation for each row.  I add the Failure rate column up and devide by the total number of failures to get the Mean Time Between failure.  


Row 1:
******
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate   FailureRate
ac12345     12345         1/1/94                     1            6          5/6/98                 5/8/98                         3                     (blank)                1586

**Note: I used the DateDiiff Between Part_Install_Date and RepairStartDate.

Row2:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate   FalureRate
ac12345     12345         1/1/94                     2            5          7/1/99                 7/21/99                       3                     (blank)                  419

**Note: I used the DateDiiff Between RepairStartDate And RepairClose Date because the same AutoID was listed more than once in the same filter set.

Row3:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate  Failure Rate
ac12345     12345         1/1/94                     3            2          5/3/01                 (blank)                        3                     (blank)                   0

**Note: I could not use the DateDiiff Between RepairStartDate And RepairClose Date because the RepairCloseDate is blank and/or 0.  So I skip this row and moe to the next rowin the same filter set.

Row4:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate  Failure Rate
ac12345     99999         5/1/94                     5            3          6/2/00                 7/1/00                         3                      (blank)             2344

**Note: I used the DateDiiff Between Part_Install_Date and RepairStartDate and moved to the next row after I checked to see if this AutoID is listed more than once.

Row5:
*****
PART#   AUTOID   Part_Install_Date   Time 1    Time2   RepairStartDate    RepairClose Date    PartChangeNumber  NewPartinstallDate  Failure Rate
ac12345     88888         8/1/95                     4            2          9/1/05                 9/2/05                         3                     (blank)                4261

**Note: I used the DateDiiff Between Part_Install_Date and RepairStartDate and moved to the next row after I checked to see if this AutoID is listed more than once.  

**NOTE: Since this is the last record in the filter, I am able to put this information into a table and run reports off of or use a form with a combo box attached to lookup a specific part and see the failure information and/or a list of all AutoIds (cars & owners) that had maintenance work done on their car for part ac12345.

My Results are:
1. Part ac12345 failed: 5 times
2. Part ac12345 Railure rate is: 8610 (Sum up the Failure Rate Column)
3. Part Mean time between failure is:  5.87... (I round the numbers so Access displayes the value as 6 -- Part failure # devided by Failure rate number)
4. Part Repair Hour Total is: 33 ( And Mean Time Between Failure for Part Repair Hour Total is: 33 devided by the total # of failures = 6.6 (7)


This is my defenition for doing the meantime between failure using the datediff.

Mean Time Between Failures: (MTBF, or "Mean Time Between Faults") The average time (usually expressed in hours) that a AUTOID component works without failure. It is calculated by dividing the total number of failures into the total number of operating hours observed. The term can also mean the length of time a user may reasonably expect a device or system to work before an incapacitating fault occurs.

MTBF Forumla  
____________L____________ = MTBF (= T Average number of hours/days).  
FRN (FR1 + FR2 + FR3, etc...)  
FRN (Failure Rate of each AutoID Component) = total number of failures  
L (Length of time) = The average time in days the system has ran without a failure  
H (Hours) = 8,760 hours per year (365 days x 24 hours per day) / life expectancy of system in hours/days  

Thank you again in advance
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 58

Accepted Solution

by:
harfang earned 300 total points
ID: 16882454
cesemj,

Thank you for your detailed explanation. However, I'm afraid I will have to correct your math.

Row 3: This row describes a failure. If you are interested in failure rates, this row shows that the part entered repair the 5/3/2001, 652 days after being last repaired the 7/21/99. The fact that it has not yet been repaired is irrelevant (although it would be for the repair time, naturally)

Rows 4&5: Your datediff's are wrong, they should be 2224 and 3684 days. You obviously used the install date of AutoID 12345 – 1/1/94, and not those of 99999 and 88888.

Calculations:

1. It failed 4 times. As you eliminated a row, your total is based on four rows only. You cannot use five as number of failures.

2. With four rows only and the installation date 1/1/94 for the three Auto's, this would be correct. It would then mean:

"The part ac12345 has been running in total 8610 days with four failures."

3. Interesting figure. It could (almost) be:

    5.0871 = 5 / 8610 * 365 * 24

This number would mean:

"The part number ac12345 is expected to fail 5.1 times in a period of 24 years..."

That seems coherent, because 8610 days are a little over 23.5 years, and you observed five failures (only four in fact, but that's another matter).

4. Interrestingly, you now include row 3 to obtain 33 hours of repair, although the repair of the last incident might not have been completed. Here, you should in fact elimintat the row.

By the way, this figure is simply the "mean repair time [in hours]", not the "Mean Time Between Failure for Part Repair Hour", which is totally misleading. You do not use any "between failure" information in this calculation.


Here are my results

 • Times to failure:   1586, 419, 652, 2224, 3684 [days]
 • Failures:              5
 • Total run time:     8'565 [days] = 23.47 [years] = 205'560 [hours]
 • MTBF:               1713 [days] = 4.69 [years] = 41'112 [hours]
 • Expected failures: 0.00058 [per day] = 0.21 [per year]  {=5.1 [in 24 years]}

As you see, the ratio 8'760 hours/year can never be used. The values you have are in days, not in years or hours. You could of course use DateDiff('h',...) to get all values in hours instead of days.

Finally:

 • Number of repairs: 4 completed
 • Total repair time: 28 [hours]
 • Mean repair time: 7 [hours]


While trying to figure out your results, I noticed a slight (and possibly irrelevant) error in the query Figure2. It should use this to follow exactly your definition:

    CVDate(Nz(NewPartInstallDate,Part_Install_Date)) AS InstallDate,

The column MeanDays in Figure3 is in fact the MTBF in days.

Happy computing!

(°v°)
0
 

Author Comment

by:cesemj
ID: 16882913
Mr. harfang You are Right.

Problem Review:
************
1. Row 3 -5 had invalid numbers failure rate numbers and yes I did use the worng dates for 4 & 5.  
2. I should not nhave included row 3 since the incident was not complete.  
3. I will correct my headings to make sure they are not misleading.

I will review my logic and work on this.

Thank you again : >)

0
 

Author Comment

by:cesemj
ID: 16932368
Hello,
I'm  receive the following error When I run the statement below: "At Most one record must be returned by this subquery" and when I click ok all the data changes to #Name?

SELECT
    Figure1.*,
    CVDate(Nz(Part_Install_Date,NewPartInstallDate)) AS InstallDate,
    (   Select Top 1 RepairCloseDate
        From Figure1 As TMP
        Where [Part#] = Figure1.[Part#]
            And AUTOID = Figure1.AUTOID
            And RepairCloseDate<Figure1.RepairStartDate
        Order by RepairCloseDate Desc
    ) AS LastRepairDate,
    RepairStartDate-CVDate(Nz(LastRepairDate,InstallDate)) AS DaysToFailure
FROM Figure1;

0
 

Author Comment

by:cesemj
ID: 16932618
I noticed that the statement will work in Access 2003 if I remove: Order by date_closing Desc.  I also noticed that the results are not the same.  The select statement above displays the results the way I need them up untilt I receive the pop up Subquery message.  But, when I run the select statement without the Orderby I do not get the error message and the results are not the same for the LASTREPAIRDATE field.  I also noticed the query returns 1778 records out of 1791.    Any Suggestions?
0
 
LVL 58

Expert Comment

by:harfang
ID: 16934450
cesemj

The error message "At Most one record must be returned by this subquery" means that the subquery finds duplicate candidates for the "Top 1". This is only a problem in conjuntion with the ORDER BY clause, but in this case we need it.

It simply means that you have at least one case where there are two records for the same Part# and the same AutoID, with the same date_closing date. To get rid of this duplicate output (which is not allowed in this subquery), you need to add the DISTINCT modifier:

    ....
    (   Select Distinct Top 1 RepairCloseDate
        ....

This does not explain why there are missing records from the output. I don't think that this can be caused by the subquery error... but do try it. Normally, Figure2 should have the same number of records than Figure1, since there is no WHERE clause.

You could try to change the main query from "SELECT" to "SELECT DISTINCTROW", although, again, it should have no effect in this case.

Tell me how it goes...

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16934474
Come to think of it, duplicate values in RepairCloseDate will disrupt your statistics. Please check your data first and find out if this duplicate has a good reason to be there. The correction using Distinct will eliminate the error but produce wrong results in the end...

(°v°)
0
 

Author Comment

by:cesemj
ID: 16935133
Hello,
I modified the select statement and inserted Select Distinct Top 1 RepairCloseDate as you said and the query ran as you said.  Additionally, I ran the query twice and the first time it returned 1790 records and the second time it returned the correct 1791 like you said.  I will do a before and after to identify why the total record count changes.


Thank you again for your guidance,

cesemj
0
 
LVL 58

Expert Comment

by:harfang
ID: 16935178
Do your records have a unique ID? If they have, you should probably remove the Distinct and add the ID to the ORDER BY, like this:

            ...
        Order by RepairCloseDate Desc, ID
    ) ...

This would be a better way to ensure that the subquery does not return duplicates.

Cheers!
(°v°)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

14 Experts available now in Live!

Get 1:1 Help Now