locked
Populate Next Inspection Date in Form RRS feed

  • Question

  • I've got a form (frmBMedEquipment) and a subform (frmBMedInventoryDatesSubform) that has a one to many relationship.  The form has equipment listed and the subform has the fields for InspectionDate and NextInspectionDate.  The subform is in datasheet view where you can see all the past inspections of the parent form equipment for the equipment record you are on.  The subform is sorted from most recent so the most recent inspection is the first record.  What I'm hoping to do is have it when an inspection date is entered for a piece of equipment, the NextInspectionDate field will automatically populate X number of days out until the next inspection.  If I add a field (call it frequency) in the parent form where I enter say 365 (1-year) for how often the piece of equipment needs to be inspected, how can I have it to where when the InspectionDate is entered the NextInspectionDate will add the InspectionDate field and the frequency field and populate the future date and automatically add this date to the NextInspectionDate field.

    TAK

    Tuesday, February 14, 2012 5:10 PM

Answers

  • The first question is whether you need the NextInspectionDate column in the table at all.  If it is immutably determined by the InspectionDate then you don't need a separate column in the table for the next inspection date.  More importantly, you should not have one because to do so introduces redundancy and the possibility of update anomalies in that there is nothing to stop one or both dates being changed so that they are not the standard frquency apart.  In the database relational model all non-key columns should be determined solely by the  whole of the key of the table.  In this case NextInspectionDate would also be determined by InspectionDate; it's transitively determined by the key, which means the table is not correctly normalized.

    So if this is the case delete the NextInspectionDate column form the table and either include an unbound computed control in the subform, with a ControlSource property of =DateAdd("d",[Frequency],[InspectionDate]) or include a computed column in the subform's underlying query, using a similar expression, and bind a control in the subform to this column.

    If, on the other hand, the next inspection date is not immutably determined by the InspectionDate, but can be legitimately adjusted so that it is not exactly the standard interval later than InspectionDate then you should have a NextInspectionDate column in the table and a bound control in the subform.  In this case put the following in the AfterUpdate event procedures of both the Frequency and the InspectionDate control:

    If Not IsNull(Me.InspectionDate) And Not IsNull(Me.Frequency) Then
        Me.NextInspectionDate = DateAdd("d",[Frequency],[InspectionDate])
    Else
        Me.NextInspectionDate = Null
    End If

    i.e. you are assigning a value to the NextInspectionDate  bound control, using the same expression, and therefore the column to which it's bound, rather than computing it in an unbound control.  The value can then be edited if necessary.

    You might find it's more convenient to use weeks as the Frequency unit, so equipment subject to weekly inspection would have a value of 1, that subject to quarterly inspection 13, and that subject to annual inspection 52 (not an exact year of course, but usually satisfactory).  The expression to compute or assign the next inspection date would then become:

    DateAdd("ww",[InspectionFrequency],[InspectionDate])

    I have as it happens had occasion to do something similar, in my case for the scheduling of preventative maintenance tasks (PMs) on engineering structures.  The frequency was stored in weeks, but rather than simply computing the date of the next task the complete maintenance schedule for a variable period ahead was computed.  This was a little more complex as it involved writing code to roll the schedule forward.  I doubt you'll need to do this, but if you do, get back to me and I can post the code here.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, February 14, 2012 6:47 PM minor clarification
    • Marked as answer by Bruce Song Tuesday, February 21, 2012 7:40 AM
    Tuesday, February 14, 2012 6:46 PM
  • I've got a form (frmBMedEquipment) and a subform (frmBMedInventoryDatesSubform) that has a one to many relationship.  The form has equipment listed and the subform has the fields for InspectionDate and NextInspectionDate.  The subform is in datasheet view where you can see all the past inspections of the parent form equipment for the equipment record you are on.  The subform is sorted from most recent so the most recent inspection is the first record.  What I'm hoping to do is have it when an inspection date is entered for a piece of equipment, the NextInspectionDate field will automatically populate X number of days out until the next inspection.  If I add a field (call it frequency) in the parent form where I enter say 365 (1-year) for how often the piece of equipment needs to be inspected, how can I have it to where when the InspectionDate is entered the NextInspectionDate will add the InspectionDate field and the frequency field and populate the future date and automatically add this date to the NextInspectionDate field.

    TAK

    Hi TAK,

    I have build a comparable system for preventive maintenance.
    It was for a Company consisting of different Departments, each Department had many Equipments installed, and on each Equipment there were one or more Task defined for preventive maintenance.

    All these Tasks were in a Task_tbl, among others defined by a Task_name, a Due_date and an Interval. The Interval contains a number and a time unit, e.g. 4 weeks, 6 months, 1 year.

    Every day a routines runs to start Actions for those Tasks with a Due_date <= Today. The Due_date is changed to Due_date + Interval, And the new Action (collected in an Action_tbl) gets an Action_date equal to the new Due_Date.
    So every time the Due_date is reached or passed for a specific Task, the Due_date is changed to the next Due_date. and the next Action is started.

    Actions must be executed. They get than an Execution_date. Empty Execution_dates mean work to be done. The difference between Action_date and Execution_date can be used as a performance indicator.

    Changing the value of the Interval is reflected in the Action_date through code.

    The system was optimized towards the creation of Actions. This is somewhat different from a long(er) term planning of work capacity.

    Feel free to ask more information.

    Imb.

    • Marked as answer by Bruce Song Tuesday, February 21, 2012 7:40 AM
    Tuesday, February 14, 2012 11:31 PM

All replies

  • The first question is whether you need the NextInspectionDate column in the table at all.  If it is immutably determined by the InspectionDate then you don't need a separate column in the table for the next inspection date.  More importantly, you should not have one because to do so introduces redundancy and the possibility of update anomalies in that there is nothing to stop one or both dates being changed so that they are not the standard frquency apart.  In the database relational model all non-key columns should be determined solely by the  whole of the key of the table.  In this case NextInspectionDate would also be determined by InspectionDate; it's transitively determined by the key, which means the table is not correctly normalized.

    So if this is the case delete the NextInspectionDate column form the table and either include an unbound computed control in the subform, with a ControlSource property of =DateAdd("d",[Frequency],[InspectionDate]) or include a computed column in the subform's underlying query, using a similar expression, and bind a control in the subform to this column.

    If, on the other hand, the next inspection date is not immutably determined by the InspectionDate, but can be legitimately adjusted so that it is not exactly the standard interval later than InspectionDate then you should have a NextInspectionDate column in the table and a bound control in the subform.  In this case put the following in the AfterUpdate event procedures of both the Frequency and the InspectionDate control:

    If Not IsNull(Me.InspectionDate) And Not IsNull(Me.Frequency) Then
        Me.NextInspectionDate = DateAdd("d",[Frequency],[InspectionDate])
    Else
        Me.NextInspectionDate = Null
    End If

    i.e. you are assigning a value to the NextInspectionDate  bound control, using the same expression, and therefore the column to which it's bound, rather than computing it in an unbound control.  The value can then be edited if necessary.

    You might find it's more convenient to use weeks as the Frequency unit, so equipment subject to weekly inspection would have a value of 1, that subject to quarterly inspection 13, and that subject to annual inspection 52 (not an exact year of course, but usually satisfactory).  The expression to compute or assign the next inspection date would then become:

    DateAdd("ww",[InspectionFrequency],[InspectionDate])

    I have as it happens had occasion to do something similar, in my case for the scheduling of preventative maintenance tasks (PMs) on engineering structures.  The frequency was stored in weeks, but rather than simply computing the date of the next task the complete maintenance schedule for a variable period ahead was computed.  This was a little more complex as it involved writing code to roll the schedule forward.  I doubt you'll need to do this, but if you do, get back to me and I can post the code here.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, February 14, 2012 6:47 PM minor clarification
    • Marked as answer by Bruce Song Tuesday, February 21, 2012 7:40 AM
    Tuesday, February 14, 2012 6:46 PM
  • I've got a form (frmBMedEquipment) and a subform (frmBMedInventoryDatesSubform) that has a one to many relationship.  The form has equipment listed and the subform has the fields for InspectionDate and NextInspectionDate.  The subform is in datasheet view where you can see all the past inspections of the parent form equipment for the equipment record you are on.  The subform is sorted from most recent so the most recent inspection is the first record.  What I'm hoping to do is have it when an inspection date is entered for a piece of equipment, the NextInspectionDate field will automatically populate X number of days out until the next inspection.  If I add a field (call it frequency) in the parent form where I enter say 365 (1-year) for how often the piece of equipment needs to be inspected, how can I have it to where when the InspectionDate is entered the NextInspectionDate will add the InspectionDate field and the frequency field and populate the future date and automatically add this date to the NextInspectionDate field.

    TAK

    Hi TAK,

    I have build a comparable system for preventive maintenance.
    It was for a Company consisting of different Departments, each Department had many Equipments installed, and on each Equipment there were one or more Task defined for preventive maintenance.

    All these Tasks were in a Task_tbl, among others defined by a Task_name, a Due_date and an Interval. The Interval contains a number and a time unit, e.g. 4 weeks, 6 months, 1 year.

    Every day a routines runs to start Actions for those Tasks with a Due_date <= Today. The Due_date is changed to Due_date + Interval, And the new Action (collected in an Action_tbl) gets an Action_date equal to the new Due_Date.
    So every time the Due_date is reached or passed for a specific Task, the Due_date is changed to the next Due_date. and the next Action is started.

    Actions must be executed. They get than an Execution_date. Empty Execution_dates mean work to be done. The difference between Action_date and Execution_date can be used as a performance indicator.

    Changing the value of the Interval is reflected in the Action_date through code.

    The system was optimized towards the creation of Actions. This is somewhat different from a long(er) term planning of work capacity.

    Feel free to ask more information.

    Imb.

    • Marked as answer by Bruce Song Tuesday, February 21, 2012 7:40 AM
    Tuesday, February 14, 2012 11:31 PM
  • Here are some earlier post on the subject --

    You need a ServiceReq table listing the services and interval. Then a VehicleSvcReq the has vehicle ID, ServiceReq, Method. Method is whether next service is the last schedule plus interval or last completion plus interval.

    The interval needs to be the lowest common denominator of all services such as weeks if any one of the services is to be accomplished on a weekly basis - bi-weekly - monthly - quarterly. All intervals will be multiples of the selected interval. If fluid checks are weekly and oil change every three months then oil change would be interval 13 - 13 weeks.

    ----------------------- ---------------

    In a Task table have a field indicating interval number for the maintenance. Use the lowest common denominator such as weeks, months or quarters. If you cannot make it work with the lowest common denominator then use two fields, one for interval type and other for numerical --

    m 2 - for 2 months

    d 30 - for 30 days

    q 2 - for 2 quarters

    Use these in DateAdd function to create your workorders using an append query.

    Another thing to think about is whether to schedule based on last performed date or straight calendar. If a maintenance task was performed late or earlier should the next one be from the completion date or whenever the calendar says it should be. Have a field in the task table indicating which if you have mixed.

    The workorder needs a date field for DueDate and Completed. The append query will look at task table for interval information and which date to use  last completed or last scheduled

     

    Wednesday, February 15, 2012 4:55 AM
  • Sorry I haven't responded earlier.  I've been out of town and couldn't check on my post.  I'm waiting on the frequency of when each piece of equipment are suppose to be inspected and my guess is that they would need to be inspected either every 6 months or yearly.  I'll probably use your first suggestion:

    "So if this is the case delete the NextInspectionDate column form the table and either include an unbound computed control in the subform, with a ControlSource property of =DateAdd("d",[Frequency],[InspectionDate]) or include a computed column in the subform's underlying query, using a similar expression, and bind a control in the subform to this column."

    If I use this option, I would assume I'd have the "Frequency" field in the parent form be a number field and just enter number of days (ex: 365 (1-year) or 180 (6 months)) in this field.  Add the controls and then it would populate the unbound subform control or new query column (whichever one I do).  These frequency fields don't need to be exactly to the day.  The technician just needs to know the next inspection is coming due. 

    I was wondering when I print an "Inspections coming Due" report, how would I just show the most recent inspection date in the report for each piece of equipment?


    TAK

    Tuesday, February 21, 2012 5:28 PM
  • That's right.  Essentially the guiding rule is 'if you can compute it, don't store it'.  That way the values can never be inconsistent.

    To return the latest inspection date per equipment item you can use a subquery to restrict a query, e.g.

    SELECT [Equipment].*, EI1.[InspectionDate],
    DateAdd("d", [Frequency], [InspectionDate] AS [DueDate]
    FROM [Equipment]  INNER JOIN [EquipmentInspections] AS EI1
    ON [Equipment].[EquipmentID] = EI1.[EquipmentID]
    WHERE [InspectionDate] =
         (SELECT MAX([InspectionDate])
          FROM [EquipmentInspections] AS EI2
          WHERE EI2.[EquipmentID] = EI1.[EquipmentID]);

    where Equipment is a table with one row per equipment item and EquipmentInspections is a related table with columns EquipmentID and InspectionDate.  Base the report on this query.

    An alternative is to create one query which returns the latest date per EquipmentID:

    SELECT [EquipmentID], [Frequency],MAX([InspectionDate]) AS [LatestInspection]
    FROM [EquipmentInspections]
    GROUP BY [EquipmentID], [Frequency];

    and then join this to the Equipment table on EquipmentID and [InspectionDate]/[LatestInspection] in another query.  The Due date would be computed in this query in the same way.  This can be done entirely in query design view, whereas the first, single query, solution requires some SQL to be written, but that's not difficult of course.

    Ken Sheridan, Stafford, England

    Tuesday, February 21, 2012 6:35 PM