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
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
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.
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.
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.
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.
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
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"
If rst1.RecordCount > 0 Then rst1.MoveLast