Create basic DB for maintenance tasks

[TeaBase]

Hello, very basic questions from me :


I need to create a schedule for maintenance tasks at work - was going to use Excel or Google Calender but thought that Access might be more appropriate, and I would like to use it


So I'm currently going about setting up the DB, I figure that I only have 4 fields.


1 - item (Boiler, Gas etc)
2 - Last review Date
3 - Next review date (though I'd have thought this could be generated with a formula using the last review date)


4 - Contractor contact details (although we use the same person for all of it I believe... still...)






Then after all of this information has been input I can try to generate scheduled alarms from it.

thanks

[ItsMe]

Not sure I see a question in your post so I will simply critique your proposal. I see a desire to create an events table. Item one looks like an FK. Item 2 looks like a field. This could possibly have a Date() as default and a "Data Entry" form could be used to create new records. Item 3 looks like an unbound textbox on a report and or form. Item 4 looks like an FK.

I would add additional fields to document the type of maintenance and repairs done. This could be two fields, an FK to a list of types of Maintenance (ie schedule A, B, C, etc) and a text or memo fields for notes. You could also include a series of Booleans to indicate pass or fail status for items inspected. Since the task is contracted, perhaps a field with the path for the directory where a scanned copy of the service document provided by the contractor could be put in place of a series of Booleans.

[TeaBase]

Not sure I see a question in your post so I will simply critique your proposal. I see a desire to create an events table. Item one looks like an FK. Item 2 looks like a field. This could possibly have a Date() as default and a "Data Entry" form could be used to create new records. Item 3 looks like an unbound textbox on a report and or form. Item 4 looks like an FK.

I would add additional fields to document the type of maintenance and repairs done. This could be two fields, an FK to a list of types of Maintenance (ie schedule A, B, C, etc) and a text or memo fields for notes. You could also include a series of Booleans to indicate pass or fail status for items inspected. Since the task is contracted, perhaps a field with the path for the directory where a scanned copy of the service document provided by the contractor could be put in place of a series of Booleans.

Cheers ItsMe...

Why is 3 an unbound text box where as 2 is a field? (Is this so a formula can be input?)

Thanks for the other suggestions, I think that If anything doesn't pass it will be resolved by those reviewing it, and not by our company. Although perhaps it wouldn't hurt to have the field in case some thing large like a boiler did require replacing?

So based off your suggestions I now (think I?) have the following tables :

1 : Item FK
2 : Last review date
3 : Next review date? (Or should this just be generated on a form?)
4 : Contractor contact details
5 : Pass yes / no, If no then fail information

I think at least, I'm struggling to think straight today thanks to an impacting wisdom tooth.... But at the best of times I don't know how to go about designing something even as simple as this

thanks

[ItsMe]

Since the date for the next inspection is on a schedule, you could give the calcualtion its own field. In other words, a periodic inspection date does not vary until after the next inspection is done.


maybe something like this....

tblMaintenance

InspEventPK (Autonumber)
UnitFK (Integer) if you could have more than 20,000 units use Long Integer
ContractorFK (Integer)
InspClassFK (Integer)
InspDate (Date/Time) Default value = Date()
InspRep (Yes/No) The value of Yes indicates this is scheduled inspection vs. repair
ServiceComplete (Yes/No) Changing value to yes can initiate calc for next insp and update InspComplete field
InspComplete (Date/Time)
InspDue (Date/Time)
InspNotes (Memo)

[Dal Jeanis]

You can use Access to automate an excel spreadsheet into a trivial database, or you can think it all through first and build an application that will automatically handle many of your needs. The question is how much time you are willing and able to put in.

If you decide to do it up right, then you need to start by designing the database to meet your needs.

Take a trip over to MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/forum/topic238.html and read his quick tutorials on database design. Do any two of his practice examples. You'll save dozens of hours of headbanging later by spending a couple of hours now.
I think you will have a table for items, one or two tables for contractors, and a table for the results of reviews.

The item table will have a key, the item name, some notes, and a review/maintenance frequency. It might also have a location. For the moment, let's say that the Frequency field is stored in terms of Days between maintenance events. If an item is scheduled to be maintenanced every 3 months, you'd store that as 90 days. (If a singel item might have two or more kinds of maintenance on different schedules, then you'd need to split this into two tables, one for the Items themselves, and one for each item's maintenance schedule.

tblItems
  ItemID      Autokey
  ItemName    Text
  Notes       memo
  ReviewFreq  Number   (this tells the system how often to review the item)

or

tblItems
  ItemID      Autokey
  ItemName    Text
  Notes       memo
tblRequiredMaint
  ReqID       AutoKey
  ItemID      Number (FK to tblItems)
  ReviewFreq  Number   (this tells the system how often to review the item)
  MaintType   Text     (if you have a maint type table)
  Notes       Memo     (explains what kind of maint if you don't have a maint table)

The contractor table, for now, can just have a key and all the contact information. If you get more contacts later, or a single contractor turns out to have three different addresses and several phone numbers, then we can break the table up into chunks.

tblContractor
  ContID      AutoKey
  ContName    Text
  ContPhone   Text
  Notes       memo

The review table documents what maintenance events have occurred. If you want to put a status on it, then these records can document future scheduled events as well. You would probably have a "fake" contractor on the contractor table for future scheduled maintenance that has not yet been assigned to a contractor. As ItsMe suggested, you could have different kinds of maintenance records, in which case you'd have a field for maint Type. Otherwise, that would be handled by the notes field.

tblReviews
  RevID       Autokey
  RevDate     Date
  ItemID      Number - FK to tblItems
  ContID      Number - FK to tblContractor 
  MaintType   Text     (if you have a maint type table)
  Notes       Memo     (explains what kind of maint if you don't have a maint table)
  Status      (Text)