Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    Inspection Date Intervals

    Not sure if I am in the right place, I have a vehicle maintenance schedule database, which works very efficiently predicting the next service based in an inspection interval number of weeks, so if the service was today a report run in 6 weeks time will show this vehicle needs it service. What I have been asked is a service schedule that shows weekly, monthly, 3 monthly, 6 monthly and yearly, so one vehicle needs to be serviced every week, then after 4 weeks a monthly, then weekly again etc until 6 months then a 6 monthly and so on hope that makes sense, having a printout every week is fine and shows the vehicle popping up on the report every week, what I need to show on the report is to show the workshop it is a weekly or a monthly or a 6 monthly etc so they can do the necessary service for the time period. I may be approaching this the wrong way, but I am at a loss at the moment. Your help would be greatly appreciated. on the Maintenance form I have the job date then a weekly, monthly, 3 monthly etc tick box to show it was that type of service, if that helps.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So the report is produced every day but a car would only show up if it is scheduled for weekly, monthly or 6 month service? And you just need the report to indicate if it is a weekly, monthly or 6 month service? So the time intervals would be like below?

    week1, week2, week3, month1
    week5, week6, week7, month2
    week9, week10, week11, month3
    week13, week14, week15, month4
    week17, week18, week19, month5
    week21, week22, week23, month6 (6 month service)
    week25, week26, week27, month7
    ...

    How far out does it go, when does the car get removed from the service report? I think with DateAdd and DateDiff functions you should be able to do what you need.

  3. #3
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi and thanks for your reply, yes you have that correct, and yes those two functions would be right I think, but how to use them correctly has totally eluded me, a little push in the right direction would be of great help thanks.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've done this with mileages, but it should work with time too. I have tables for vehicles, service types (oil change, transmission service, etc), services performed and scheduled service intervals (vehicle number, service code, mileage). Using that table with the table of services performed, I can easily provide a report of what's due for service. I think it would be easiest to use a common interval like days or weeks.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi, and thanks, yes we have a 6 weekly, a 12 weekly service schedule in place which works fine, because it just roles over on those intervals but I am now needing to let the engineers know which service interval it is from the last service, is it a weekly or a 6 monthly or a 3 monthly etc. that's my issue. I just someone to give me pint in the right direction.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO, not enough has been said about the inspections. Are they all the same or is the monthly one more in depth than the weekly? In fact, for any inspection interval that coincides with one whose frequency is less, is the less frequent one the same, or is it comprised of the more frequent one plus additional work? Or is it an add-on that has no similarity to the more frequent ones? The reason I ask is the approach would depend on what these inspections are comprised of and how you have set them up. If they are all the same, you only need to retrieve the inspection that is due, but I suspect that is not the case here. However, if this has been set in a schedule, what happens if (for example) the cycle is interrupted due to a vehicle being taken out of service for a week for accident repair, or an inspection is simply missed? Your whole schedule could be in disarray.

    On the other hand, you might be treating the less frequent inspection as an add-on. In that case, you'll need to retrieve either single or multiple inspections with a higher frequency and append them to the less frequent one. In addition, if the inspection results are going to become part of the data set, then consider a data model where the task to perform work on the vehicle is a route, the task to inspect a system (e.g. steering) is an activity and the result is an indicator. If you take this approach, you can add inspections to the data set that have nothing to do with vehicles, such as buildings or process lines should you have any that need attention. I have worked in equipment reliability for many years, so I might be able to provide some guidance if I knew more about the process. I might even suggest that time based inspection systems make less sense than usage based ones because unnecessary work gets performed on assets that have had little or no use in between inspection dates.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi and thanks, I get where youre coming from but timed based inspections have to be observed because we have to follow FTA and VOSA criteria. Therefore they have to be time based. I do like your thinking. But I need to pursue the time based system, therefore I need a little help in producing a weekly report that informs the engineers what perticular inspection/service it is they need to carry out.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I used 30 days for 1 month and 180 days for 6 months, not sure if that will work. But see if this might work. I guess this could go in a query column also with a long IIF statement.

    vDays = DateDiff("d", InitialServiceDate, Date)

    vDays = 60 'Use this to test with. If it works remove this line.

    If Len(Int(vDays / 180)) = Len(vDays / 180) Then
    ServiceType = "6 Month Service"
    Else
    If Len(Int(vDays / 30)) = Len(vDays / 30) Then
    ServiceType = "Monthly Service"
    Else
    If Len(Int(vDays / 7)) = Len(vDays / 7) Then
    ServiceType = "Weekly Service"
    Else
    ServiceType = "No Service Needed"
    End If
    End If
    End If

  9. #9
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Thanks, I like your thinking, thats more or les I am looking for, I just need to implement it and where to place it, but I would have never gone down route with my very little knowledge of access programing, so thanks you it gives me a great foundation to work from. I am in a meeting tomorrow, but will be able to trial it in a few days and will keep you informed.

  10. #10
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Could this be worked on a week basis replacing the "d" with "ww" and changing the 7 days to 1 and the 180 to 26

  11. #11
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi Bulzie,thanks for that bit of code, I placed it in a long iif statement, and as far as I can see works out just fine, however, how do I use it to predict the next service date, I am assuming that the weekly service is based on the date difference between date() and initialservicedate? am I correct?

  12. #12
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    This is what I have so far
    vDays: DateDiff("d",[newjobdate],Date()) in one query column then in another
    Service Type: IIf(Len(Int([vDays]/168))=Len([vDays]/168),"6 Month Service",IIf(Len(Int([vDays]/336))=Len([vDays]/336),"Yearly Service",IIf(Len(Int([vDays]/30))=Len([vDays]/30),"Monthly Service",IIf(Len(Int([vDays]/7))=Len([vDays]/7),"Weekly Service"))))

    Could you please confirm this is correct?

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Yes it should work using weeks instead of days but you will need to test to see if that logic will work for what you want. And this is just one possible path, others might suggest alternate methods.

    vWeeks = DateDiff("ww", InitialServiceDate, Date)

    vWeeks = 1 'Use this to test with. If it works remove this line.

    If Len(Int(vWeeks / 26)) = Len(vWeeks / 26) Then
    ServiceType = "6 Month Service"
    Else
    If Len(Int(vWeeks / 4)) = Len(vWeeks / 4) Then
    ServiceType = "Monthly Service"
    Else
    If Len(Int(vWeeks / 1)) = Len(vWeeks / 1) Then
    ServiceType = "Weekly Service"
    Else
    ServiceType = "No Service Needed"
    End If
    End If
    End If

    If you put this as a column in the query for the report, would be something like below.

    ServiceType: IIF(Len(Int(DateDiff("ww", InitialServiceDate, Date) / 26)) = Len(DateDiff("ww", InitialServiceDate, Date) / 26), "6 Month Service", IIF(Len(Int(DateDiff("ww", InitialServiceDate, Date) / 4)) = Len(DateDiff("ww", InitialServiceDate, Date) / 4), "1 Month Service", IIF(Len(Int(DateDiff("ww", InitialServiceDate, Date) / 1)) = Len(DateDiff("ww", InitialServiceDate, Date) / 1), "1 Week Service", "No Service")))

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Yes that logic looks right but you will need to test it to see if all syntax is right. You should also be able to replace vDays in the 2nd line with the actual formula if needed if this is going in a query. Will make it a lot longer but will all be in 1 column.

  15. #15
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    where you have ServiceType: IIF(Len(Int(DateDiff("ww", InitialServiceDate, Date) / 26))should Date be Date()?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Part Inspection Database Help
    By yoseph12 in forum Access
    Replies: 5
    Last Post: 12-20-2015, 02:21 PM
  2. Inspection report
    By FJM in forum Access
    Replies: 3
    Last Post: 09-24-2014, 05:50 AM
  3. Hydrant Inspection Database Help
    By Plan B in forum Database Design
    Replies: 12
    Last Post: 01-29-2013, 01:51 PM
  4. Time intervals
    By Dutch1956 in forum Queries
    Replies: 5
    Last Post: 07-12-2012, 11:21 PM
  5. Help with Time Intervals
    By ddog171 in forum Queries
    Replies: 3
    Last Post: 03-07-2006, 06:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums