Results 1 to 2 of 2
  1. #1
    shariq1989 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    50

    Maintenance Database

    I am trying to put together a database for the maintenance tracking and planning of components on various vessels. I am having issues coming up with a schema that works and a good way to track maintenance intervals. Here is the low down: Vessels have many components that will be tracked like propellers, shafts and engines. Many of these components will have sub-components like pistons (engines), shaft seals (shafts), etc. Each component will have a service interval and an overhaul interval which is not based on time but on operating hours. Engine component intervals depend upon the running hours of that respective engine and other components depend upon the operating hours of the vessel. The database will be sending out emails when the overhaul/service intervals are close to being due. I'm also gonna need a reset function of some sort to reset the count for a certain component if it was replaced before the interval was coming up. For example, if the bearings were to be serviced in 500 hours but they broke down and had to be replaced prematurely, the would now be needing service in 35000 hours since they are serviced every 35000 hours. I also would like the database to track the running hours of each component. This means not having the hours count of each component going to zero hours at each overhaul, but the overhaul hours adjusting accordingly. For example, pistons are serviced at 17500 hours and then overhauled at 35000 hours. Now the next service should automatically be at 35000+17500 and the next overhaul should be at 35000*2 hours. So there would be a planning part of it, where the database forecasts upcoming maintenance and an executing part of it, where the engineer can enter in what maintenances have been completed. The engineer will update operating hours for all engines and vessels at the end of each month. This seems like it could be very complex or very simple at the same time and I just don't know how to go about it.

    This is the schema I have right now:

    TblVessels
    VslID (1), VslName (Wanderer), VslTypeID (1)

    TblVslTypes
    VslTypeID, VslType (Barge)

    TblEngines
    EngineID, ManufacturerID, EngineModelNumber, VesselID, LocationID, EngineTypeID

    TblManufacturers
    ManufacturerID, ManufacturerName

    TblLocations
    LocationID, LocationName (Starboard)

    TblEngineTypes
    EngineTypeID, EngineTypeName (Aux)

    TblEngineComponents
    EngineComponentID, EngineID, EngineComponentName, Service Interval, Overhaul Interval



    TblVesselComponents
    VslCompID, VslCompName, VesselID, Service Interval, Overhaul Interval

    TblCompletedMaintenance
    OverhaulID, ComponentID, DateOverhauled

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The table schema seems easy enough. It is the queries and calcs for maintenance cycles that looks complicated.

    You might consider commercially developed software. Contact developer and describe your needs. Maybe something already exists that will do the job.

    This one has a 30-day free trial http://www.mtcpro.com/fleetmtc.htm

    Here are some for aviation components maintenance that might help with your design http://www.capterra.com/aviation-maintenance-software
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-16-2012, 12:48 AM
  2. Replies: 3
    Last Post: 05-15-2011, 10:52 PM
  3. Maintenance reminders using email (complex question)
    By avarusbrightfyre in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2011, 05:18 PM
  4. Maintenance on programs on open source
    By fsmikwen in forum Access
    Replies: 3
    Last Post: 01-15-2010, 08:43 AM
  5. Creating a Maintenance Scheduler
    By squirrel in forum Forms
    Replies: 0
    Last Post: 02-15-2006, 03:45 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