Date Calculations

[LadyDi]

I have a database that I am trying to create that will track maintenance required for various machines on the factory floor. Some machines need to be maintained monthly, some quarterly, some semi-yearly, some yearly, and some bi-weekly. I would like to set the database to automatically calculate the dates that maintenance is required. I'm having a lot of trouble getting this to work. At this point, I was just trying to generate a list of dates and I can't even get that to work.

So, if I have a machine that needs maintenance performed quarterly, I want an e-mail or a pop up message generated on January 1, April 1, July 1, and October 1 for that machine. Right now, there is someone going into a database and manually entering the dates for the first day of every quarter, the half way mark in the year, the first of every month, and even the first day of every other week (for machines that need maintained bi-weekly). I would like to eliminate this step and automate the population of the dates. Is there a way to do that?

 

[CJ_London]

yes there is but you'll need to explain a bit more:

Do you want the system to create records 'going forward' or just to show what is currently due based on today or other specified date (i.e. no records created)? in which case how do you 'catch up' if the maintenance isn't completed.

Do you want the schedule based on a start date of the 1st January of the current year - or the date the asset was commissioned or similar? Just concerned if you go for the 1st January option you will create big peaks and troughs in your maintenance workload.

Would it be better to send one email showing all maintenance due in the coming week so the maintenance team have some forewarning so they can plan better?

 

[LadyDi]

If I could, I think I would like to have a table that houses the dates. That is the way they are doing it in the database they have now. One table shows all the quarterly, monthly, bi-weekly, and yearly dates. Then another table, storing the data on the machines, references this dates table. If this is not the best method, please let me know. I am open to suggestions. The users just want a database that is more automated than what they have now. Also, I believe they are currently basing everything on January 1 of the current year. I understand what you mean about the peaks and troughs doing it this way, but I'm not sure they will let me change that one.

One e-mail showing all maintenance due in the coming week would be wonderful.

 

[CJ_London]

Just as a followon, this basic function should do what you require. If you put it in a module you can then refer to it from a query. Note this assumes a 7 day a week operation because for example, some of the 1st of months will be a Saturday or Sunday so if the routine is not run on that day, the asset will not be reported for maintenance

Start is the 1st January or commissioning date as required
ReptDate is the date of the report (based on your post that would be today)
Frequency is the frequency you require for the asset so will presumably come from the asset record.

Public Function MaintenanceDue(Start as Date, ReptDate as Date, Frequency as string) as boolean
 
    Select Case Frequency 
        Case "Monthly"
            MaintenanceDue=DateAdd("m",Datediff("m",Start,ReptDate),Start) =ReptDate
        Case "Quarterly"
            MaintenanceDue=DateAdd("m",Datediff("m",Start,ReptDate)\3,start)=ReptDate
        Case "BiAnnual"
            MaintenanceDue=DateAdd("m",Datediff("m",Start,ReptDate)\6,start)=ReptDate
        Case "Annual"
            MaintenanceDue=DateAdd("m",Datediff("m",Start,ReptDate)\12,start)=ReptDate
        Case "Fortnightly"
            MaintenanceDue=DateAdd("d",Datediff("d",Start,ReptDate)\14,start)=ReptDate
        Case Else
            msgbox "Frequency  " & Frequency & " not recognised, MaintenanceDue set to false
            MaintenanceDue=false
     End Select
 
End Function

The function will return true if it is due, otherwise false, so in your query you would set the criteria required=true

note the backslash is used so only the integer part of the calculation is returned

 

[CJ_London]

If I could, I think I would like to have a table that houses the dates

This would require a recursive query which does not exist in Access. However it can be done by using VBA to create an insert sql string and repeating for a period of time to say populate records for the next 12 months.

You would want a different function so as you parse through each asset record, you can repeat the insert sql, incrementing the maintenance date by the frequency (using the dateadd function) until the maintenance date falls into next year.

You'll also need to think how you will handle events such as new assets and scrapped assets.

 

[LadyDi]

That was very helpful. Thank you very much.

 

[gemma-the-husky]

just to clarify you need two tables

1. the asset table
2. asset service dates

for each item in 1, you need a corresponding set of items in 2, with the scheduled maintenance dates in this table, you can also add other stuff, like actual date, tester name, observations etc etc.

the above processes are discussing automating management of these records.