Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011

    Unanswered: Tracking Hours and Maintenance Layout

    I am looking for some advice on how to structure the hour tracking and mainteance tracking portion of my database. So far the database only has specfications like equipment, components, belts, filters, fluids and maintenance tasks.

    This is the structure:

    Table 1 - Equipment
    EquipmentID, EquipmentNumber, Type, Mfr, Mdl, SN

    Table 2 - Components
    ComponentID, ComponentNumber, Type, Mfr, Mdl, SN, Description, EquipmentID

    Table 3 - Belt
    BeltID, Mfr, Mdl

    Table 4 - Filter
    FilterID, Type, Mfr, Mdl

    Table 5 - Fluid
    FluidID, Type, Mfr, Mdl

    Table 6 - Maintenance
    MaintenanceID, ComponentType, ServiceType, ServiceItems, ServiceDescription

    Table 7 - ComponentBelt
    ComponentID, BeltID, Qty

    Table 8 - ComponentFilter
    ComponentID, FilterID, Qty, Location, SpecIntervalHrs, SpecIntervalMons

    Table 9 - ComponentFluid
    ComponentID, FluidID, PolarisID, Qty, QtyUnits, SpecFluid, SpecIntervalHrs, SpecIntervalMons

    Table 10 - ComponentMainteance
    ComponentID, MaintenanceID, IntervalHrs, IntervalMons, Notes

    The debate I am having is if the log for hours should be seperate from the log of services. We gather equipment hours every week and services usually occur at 250, 500, 1000, 2000 hour intervals. If they were seperate the tables would look like:


    IntervalHrs (250,500,1000,2000)

    What do you think? I am looking for advice from people that have done this kind of design before

  2. #2
    Join Date
    Aug 2011
    I should probably also state that I am going to try to use access to calculate hours since last service

  3. #3
    Join Date
    Sep 2010
    Provided Answers: 6
    I have done a similar project and took the approach that it is all about the equipment.

    If you log the equipment hours every week then this should be in one table. ComponentHrsLog. Are the hours measured on the equipment accumulative or are they re-set after each reading?

    Your equipment service history should be in a separate table. ComponentMaintenanceLog

    You could run queries on ComponentHrsLog to warn if the equipment is due/overdue for servicing.

    Whenever I approach a project I keep asking myself. What information do I need from all the data i am collecting? What is the aim? This helps me in focussing on how to approach the structure of the table.

  4. #4
    Join Date
    Aug 2011
    The hours are cumulative. Thank you for your input. I thought it might be best to keep them seperate. Every now and then I have to step back and review what I am doing in the database. I started out with a huge scheme I wanted to do but now I am breaking it down into smaller sections and completing them one at a time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts