troubled in search for answer

[krefll_ok]

Good Day,

I have been selected to design a Total Preventative Maintenace database, but have had no previous knowledge of access.

An objective (amongst others) is to print a report each month that can be posted so that every one knows what needs to be done for the month ahead.

Using a query to select data i can get a report on what has to be done next month.

The problem is this...

I need is a way of automatically creating a record specifying the new date based on the frequency when the action has been completed, or not completed, as the case may be.


table example.

Scheduled maintenance (maintenanceid, machineid, category, description, frequency, duedate, datecompleted, actionedby, downtime)

To clarify, i can enter the data once, and the task will come up on the report as and when dictated by the frequency.

Any comments would be most appreciated

 

[jon98548]

I have a similar task, I think. I was just asked to start an action item registry. The ordinary task assignment isn't the problem. We have a couple items that come up every 6 months. We don't want to go in and enter this each time, but have it show up on the action item list 60 days prior to the due date. Like a recurring appointment in Outlook. Is this kind of what you need?

 

[neileg]

Few thoughts

You could calculate the due date for maintenance for a period in the future when you buy the asset.
E.g. you buy a machine on 3 January 2003 and you will keep it 5 years. This machine needs annual maintenance. You therefore calculate maintenance due on 5 Jan 04, 5 Jan 05 etc, and create a record for each event in a table

You could calculate the next maintenance date when the current one is met. E.g. same machine. Maintenance for 2004 actually carried out on 8 Jan 2004, so next maintenance due 8 Jan 2005. So you only should have one record for maintenance in the future.

Circumstances will dicitate which one is appropriate.

Either way you will generate a table of maintenance events. These should include an indicator that tells you that the maintenance is complete. You can therefore run a query that says, tell me all of the maintenance events that have not yet been completed and that have a due date before the end of the month. This will pick up any that are due this month, and any that were due before but still have not been done.

This could generate a problem if you miss the scheduled date by more than the normal gap between maintenance. E.g. a monthly maintenance check slips by more than a month. Does this mean that two jobs need doing, or just one?

This will require two tables, one to identify the machine ( perhaps machineid, category, description, frequency) and one for the maintenance events (perhaps maintenanceid, machineid, duedate, datecompleted, actionedby, downtime). You will have one record per machine in the first table and one record per maintenance event in the second table, linked by the machineid.

The structure is straight forward, the query easy too. The thought will come around creating the maintenance due records. You could do this with an append query, or with code. Code would be neater, the append query would be easier for duffers like me.

Good luck

 

[jon98548]

Niel, thanks. That helped a ton with the structure. Kreff and I have PMed each other and figured out we're pretty much after the same thing.

Say you have maintenance due on 10Jan and 10Jun every year. If 10Jan was not completed, I still want 10Jun to be added to the registry 30 days prior. Should I have have a procedure tied to the OnClick event of a button or to the OnOpen event of a form/report.

I'm assuming that a record would have to be entered manually the first time. Then at some interval past that begin date(?) it would add a new record for the same item. Are we talking append query or a recordset manipulation? I hope I'm clear, I'm thinking with my fingers now.

Thanks again, Niel, anymore nudges?

 

[jon98548]

OK. I put a field in the main table called RecurringItem. It's a yes/no field. I made an append query for the main table looking for RecurringItem = yes, Where Date () is within 30 days from DueDate. The problem is every time this query opens, I add a new record for each recurring item in the range. Any ideas how to tag that record so it doesn't come through after it has been added?

 

[neileg]

I think I understand what you mean. You want the advantages of both alternatives that I suggested!

Having rewritten this post several times I would make the following points:

1) The service intervals 10 Jan & 10 Jun are uneven, viz. 5 months and 7 months. This may be accidental and you meant 10 July, but I can see how this might be quite reasonable.

2) If you want to allow uneven intervals like this, it makes it much harder to generate on the fly. Yes it will still be possible, but you're going to have to write some code. I would prefer to schedule the whole life maintenance at one go if you want this.

3) If you dont want uneven intervals and you are happy with six monthly, then I would generate these based on the start date of the schedule eg the purchase date:
a) Take the difference between today and the purchase date and divide by the service interval.
b) Round this up to a whole number
c) Add this number times the service interval to the purchase date and you get the next due date.
d) Test to see if this falls in the next 30 days.
e) If it does, create a new maintenance job.

4) The problem with generating jobs like this is that you have to do this, either every day and test for exactly 30 days ahead, or to do it at a fixed point and test on a date range. Otherwise you will either miss or duplicate these jobs. Generating a whole life maintenance schedule at the outset avoids this problem.

5) The advantage of generating jobs like this s that you can change the service interval and it will generate future jobs on the right date.

6) If you stick to generating these jobs one at a time, you might have a machine with differeing service tasks, eg Clean and lube weekly, 'A' service monthly, 'B' service six monthly. How are you going to handle this?

I think this sort of answers the question, but raises some others!

 

[jon98548]

Thanks again, Neil. And yes I meant 10Jul. What you have here I like and will get started on that. This is very helpful and appreciated.

Cheers

 

[jon98548]

This is coming together in my head a bit, but have a question. I have a query that will do the calculations I want (still have an issue with the rounding part). Should I put this as an event on the form BeforeUpdate? The query helps me figure out how the calcs should go, but would like to do this in code. Should I make a recordset, use SQL or a combination of the two?

 

[jon98548]

OK, I dug in and got this far.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

'Declare and instantiate object variables
Dim cnCurrent As ADODB.Connection
Dim rsRecurring As ADODB.Recordset
Dim N As Integer
Dim NxtDueDate As Date
Dim RoundN As Integer
Dim N1 As Integer

'Instantiate the objects
Set cnCurrent = CurrentProject.Connection
Set rsRecurring = New ADODB.Recordset

rsRecurring.Open "SELECT * FROM tblActionItems WHERE RecurringItem=Yes", cnCurrent
Do Until rsRecurring.EOF
    N = DateDiff("m", rsRecurring!CreateDate, Date)
    RoundN = Int((N / rsRecurring!ActFrequency) + 1)
    NxtDueDate = DateAdd("m", (RoundN * rsRecurring!ActFrequency), rsRecurring!CreateDate)
    N1 = DateDiff("d", Date, NxtDueDate)
    If N1 <= 30 Then
        DoCmd.RunSQL _
            "INSERT INTO tblActionItems ( ActionItem, RecurringItem, ActFrequency, CreateDate, " & _
                "Userid, ExtContact, SourceID, ProcessID )" & _
            "SELECT tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID " & _
            "FROM tblActionItems " & _
            "GROUP BY tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID " & _
            "HAVING (((tblActionItems.RecurringItem)=Yes));"
    End If
rsRecurring.MoveNext
Loop

rsRecurring.Close
cnCurrent.Close
Set rsRecurring = Nothing
Set cnCurrent = Nothing

Requery

Exit_Form_Open:
 Exit Sub
 
Error_Handler:
    MsgBox "An error has occurred.  The error number is " & Err.Number & _
        " and the description is " & Err.Description
    Resume Exit_Form_Open

End Sub

This runs fine but for one thing. It keeps looping. I have one record that meets my criteria and this keeps adding it in over and over. Or it loops through, sees the new record just like the old record and adds another one.

One thing I would like to do is add the new DueDate to this new record. I also feel l need to 'mark' the old (or maybe the new?)record at some point in this loop so it only goes through once for each unique record. Any of this make sense?

 

[jon98548]

In searching on endless loops I found a post where Pat asked 'Why do you think a loop is necessary?'. So, I asked myself the same question. Then took out the loop in the code I have above and whattayaknow it worked. Still have a couple of other issues to take care of. Will be back on those. Come on with your comments though.

One of those issues is making DueDate in the record I just added equal NxtDueDate.

 

[neileg]

Sorry I've gone quiet, I've been off work sick.

Your expertise in code is greater than mine. I tend to stick to queries for data manipulation. However. I'm still happy to comment on what you're doing, if you like!

 

[jon98548]

Please comment away. And sorry to hear you've been sick. Hope you're better now. 'Expertise'? HAHAHAHAHAHA. Like yousaid before, duffer.

Anyway, code aside, I open a recordset, run a calculation, and the run a query. This runs fine, but I wonder if I need it in code. Would a regular query run faster? I think getting it in code is answering a challenge for me. At this point though, every time the form is opened it runs this code while I'm within that 30 day range and adds a new record. So, I need to stop that after it has been added, but I think that will stop if I can get the new due date to go in. Here is what the code looks like at this point:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

Dim cnCurrent As ADODB.Connection
Dim rsRecurring As ADODB.Recordset
Dim N As Integer
Dim NxtDueDate As Date
Dim RoundN As Integer
Dim N1 As Integer

Set cnCurrent = CurrentProject.Connection
Set rsRecurring = New ADODB.Recordset

rsRecurring.Open _
    "SELECT * FROM tblActionItems WHERE RecurringItem=Yes " & _
    "AND ((tblActionItems.Updated)=No)", cnCurrent
    
    N = DateDiff("m", rsRecurring!CreateDate, Date)
    RoundN = Int((N / rsRecurring!ActFrequency) + 1)
    NxtDueDate = DateAdd("m", (RoundN * rsRecurring!ActFrequency), rsRecurring!CreateDate)
    N1 = DateDiff("d", Date, NxtDueDate)
    If N1 <= 30 Then
        DoCmd.RunSQL _
            "INSERT INTO tblActionItems ( ActionItem, RecurringItem, ActFrequency, CreateDate, " & _
                "Userid, ExtContact, SourceID, ProcessID, DocumentLink )" & _
            "SELECT tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID, " & _
                "tblActionItems.DocumentLink " & _
            "FROM tblActionItems " & _
            "GROUP BY tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID, " & _
                "tblActionItems.DocumentLink " & _
            "HAVING (((tblActionItems.RecurringItem)=Yes));"
    End If
rsRecurring.MoveNext

rsRecurring.Close
cnCurrent.Close
Set rsRecurring = Nothing
Set cnCurrent = Nothing

Requery

Exit_Form_Open:
 Exit Sub
 
Error_Handler:
    MsgBox "An error has occurred.  The error number is " & Err.Number & _
        " and the description is " & Err.Description
    Resume Exit_Form_Open

End Sub

Thanks.

 

[neileg]

I won't comment on the code, but the point of adding the record once and only once is what I referred to above.

4) The problem with generating jobs like this is that you have to do this, either every day and test for exactly 30 days ahead, or to do it at a fixed point and test on a date range. Otherwise you will either miss or duplicate these jobs. Generating a whole life maintenance schedule at the outset avoids this problem.

Unless I misunderstand your code, you are testing to see if a maintenance event would be due in the next 30 days. If it is, you are creating the event as a record. My inclination would be to do it the other way round, i.e. create the maintenance events whenever they are due, and then test to see if they are in the next 30 days.

I realise that I have simply restated your problem, and not proposed a solution!

As to the speed of querydefs compared to SQL, I seem to remember that Pat H has pointed out that stored querydefs always are more efficient than SQL in Access.

 

[jon98548]

OK. I did some more digging and scratching and here is what I came up with. It is working great with the test situations I have thown at it. Please look it over and share any advice or possible bugs. When my form opens it looks for recurring items and adds a new task to the list if it is due within 30 days. I haven't replaced SQL with querydefs but will if need be. Neil, you were a great help and really appreciate your time. Anyone else please feel free to comment. If no one sees any problems, this is what I will run with or a version with run query commands instead of SQL.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

'Declare and instantiate object variables
Dim cnCurrent As ADODB.Connection
Dim rsRecurring As ADODB.Recordset
Dim N As Integer
Dim NxtDueDate As Date
Dim RoundN As Integer
Dim N1 As Integer

'Instantiate the objects
Set cnCurrent = CurrentProject.Connection
Set rsRecurring = New ADODB.Recordset

'Opens a recordset with only the most recent record of an action item.
'Made the field [Updated] to mark older action items.
rsRecurring.Open _
    "SELECT * FROM tblActionItems WHERE RecurringItem=Yes " & _
    "AND ((tblActionItems.Updated)=No)", cnCurrent

'Keeps from adding the same action item with the same due date.
If DateDiff("d", rsRecurring!DueDate, Date) <= 30 Then
    GoTo Exit_Form_Open
Else
    'Calculate the next due date and how many days away.
    N = DateDiff("m", rsRecurring!FirstDue, Date)
    RoundN = Int((N / rsRecurring!ActFrequency) + 1)
    NxtDueDate = DateAdd("m", (RoundN * rsRecurring!ActFrequency), rsRecurring!FirstDue)
    N1 = DateDiff("d", Date, NxtDueDate)
    'If new due date is within 30 days,
    If N1 <= 30 Then
        'mark the oldest record of this action item as updated,
        DoCmd.SetWarnings False
        DoCmd.RunSQL _
            "UPDATE tblActionItems SET tblActionItems.Updated = Yes " & _
            "WHERE (((tblActionItems.Updated)=No) " & _
                "AND ((tblActionItems.RecurringItem)=Yes) " & _
                "AND (Not (tblActionItems.DueDate) Is Null));"
        'add a new record for this action item,
        DoCmd.RunSQL _
            "INSERT INTO tblActionItems ( ActionItem, RecurringItem, ActFrequency, CreateDate, " & _
                "Userid, ExtContact, SourceID, ProcessID, FirstDue, DocumentLink )" & _
            "SELECT tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID, " & _
                "tblActionItems.FirstDue, tblActionItems.DocumentLink " & _
            "FROM tblActionItems " & _
            "GROUP BY tblActionItems.ActionItem, tblActionItems.RecurringItem, " & _
                "tblActionItems.ActFrequency, tblActionItems.CreateDate, tblActionItems.Userid, " & _
                "tblActionItems.ExtContact, tblActionItems.SourceID, tblActionItems.ProcessID, " & _
                "tblActionItems.FirstDue, tblActionItems.DocumentLink " & _
            "HAVING (((tblActionItems.RecurringItem)=Yes));"
        'and enter the new due date.
        DoCmd.RunSQL _
            "UPDATE tblActionItems " & _
            "SET tblActionItems.DueDate = " & _
                "DateAdd(""m"",(Int((DateDiff(""m"",[FirstDue],Date()))/[ActFrequency]+1))* " & _
                "[ActFrequency],[FirstDue]) " & _
            "WHERE (((tblActionItems.RecurringItem)=Yes) AND ((tblActionItems.Updated)=No));"
        DoCmd.SetWarnings True
    End If
    rsRecurring.MoveNext
End If

rsRecurring.Close
cnCurrent.Close
Set rsRecurring = Nothing
Set cnCurrent = Nothing

'Updates the form.
Requery

Exit_Form_Open:
 Exit Sub
 
Error_Handler:
    MsgBox "An error has occurred.  The error number is " & Err.Number & _
        " and the description is " & Err.Description
    Resume Exit_Form_Open

End Sub

 

[neileg]

Looks good. I can see a flaw in the logic, though.

Some machinery requires different types of maintenance, sometimes on a diferent cycle, e.g. monthly maintenance and an annual safety check. To be able to cope with this in your model, you will have to include this as two separate machines, otherwise the monthly and annual events will not both be scheduled if they fall within the same 30 days. Even at this, you might end up with a monthly check on one day and an annual check the next. Not the most efficient way of doing it.

We're buying some software at the moment that can cope with this and merges the two events. I can't yet figure out the logic it is using.

 

[jon98548]

That's true. I hadn't thought about that one. I suppose you could handle manually. In other words, if this is a list you were checking each day, you would have to see it. Still not exactly what you would like I guess, isn't it. One thing I have come up with is in the NxtDueDate calculation. I have it set static at monthly.

NxtDueDate = DateAdd("m", (RoundN * rsRecurring!ActFrequency), rsRecurring!FirstDue)

If I had something weekly or daily, doesn't work too well does it. I guess I could have another field to ask how it occurs. Then have the calculation look at that field?

As always Neil, I appreciate your input. And any others.

Cheers.