No announcement yet.

Maintenance List?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Maintenance List?

    How would you create a list of past customers that should have scheduled maintenance on something?

    Example: Annual or Semi-Annual HVAC maintenance.

    Certain program that throws the most current one to date up to the top every day?

    Just a list to look at?

    Have about 500 that are just on paper that probably should be made into a computer list or program.

    Thanks for any ideas.

  • #2
    Re: Maintenance List?


    I guess much would depend on if you have one of the "office suites" available to you and how much time you have to input the data from your paperwork.

    You could use "Open Office" which is free and is considered to be a close match to the way-too-expensive "Microsoft Professional Office". "Microsoft Works" also has an elementary database of sorts, I believe and comes with many computer systems. There are other databases out there I'm sure, but I'm not presently familiar with what's available.

    I think many people might try to do this on a "spreadsheet", but I'm a real fan of "database" use. For example, with a situation like yours the first question I would ask is whether this information you already have is on some kind of form that you fill out for each customer and service call. If it is, then that makes the database much easier to design, as you have already catagorized everything except for possible notes or comments that you probably detail the service call with.

    In any case, with a database you simply set up a data "field" for each catagory or blank on the form. Like "last name", "first name", "street", "city", "phone", etc. You do the same with the equipment involved, like type of unit, mfg, model, date, etc.

    If you break down your customer into such catagorical fields, you then can make a form very much like your paperwork. Fill that out with each call (enter the data), and over time you will build a database of all your customers and their particulars. Once you have that data, you can sort it in anyway you want. You can produce reports, set up schedule calenders, calulate cost, time, whatever.

    I love databases as they are relatively easy to put together and so flexible as to how you report, look at, or print out the data. You can sort them in any order on any field or on any bunch of fields.

    For example, if you've catagorized (provided the proper data fields) the information, you could in a matter of seconds get a report on how many guys named "John" own "Carrier" equipment that was serviced last "July".... or do whatever.

    Hey, I set up a database for my ex-employer on all the compressors we manufactured and sold. All of our equipment is custom made using a cross-match of various frame models, cylinder sizes, valve, and control items as well as gas applications. And daily, I would receive multiple request from around the world for data requests.... Hey, have we sold in ABC units for NL applications with Hydrogen Gas using 17-inch cylinders and so-and-so valves? Within a matter of minutes I could send them a report showing the placement of all such product installations... including the company name, location, number of units, pressure and cfm, etc.

    I even use a database for all the Ham Radio testing that we do. I can tell a guy not only when he took his test, but what element number, his score, the date, and any other exam he took with us and who were the three guys who scored his exam.... Better yet, I've cross-referenced his data with my digital scan files so I can give him a copy of everything that he's ever done with us.

    A database is where I would go with this. They're fairly easy to use and in the case of something like "Open Office", they are free. The alternative of course is look for something that is already out there on the market for you industry... but that will most likely cost a good sum.

    I hope this helps... if you need further info, don't hesitate to ask,

    Last edited by CWSmith; 11-03-2012, 06:39 PM.


    • #3
      Re: Maintenance List?

      Very appreciated. I have downloaded Open Office and see what I'm able to do.

      Thanks again.


      • #4
        Re: Maintenance List?

        For recurring maintenance, include fields for the pm period, such as how often each component needs a particular service and as cws said include the date last serviced. Then you can run reports to output which customers are coming due next month and contact them to schedule your next visit.

        There are existing progams which will do all this for you, but you can roll your own as cws said using db software.
        "It's a table saw, do you know where your fingers are?" Bob D. 2006


        1/20/2017 - The Beginning of a new Error


        • #5
          Re: Maintenance List?


          I haven't done any serious database design in about ten years now (Man, it's hard to believe I've been gone from that place that long... I'm still having nightmares about it though).

          BUT, if I can offer any assistance I'll try.

          Basics are this, first thing you build is the data "table":

          You create a field (like when you design a form), decide what you want to put in it (like a "name" is a "alpha/numeric field", and you decide how many characters long it must be... like normal names would fit 18 to 20 character length.

          You do this for every catagory you want to track.

          You can even build auxiliary tables, for example you could have a customer table with certain "key" fields that would be unique to each customer (like you must have something to distinquish all the different "John Jones" that you may have as customers.

          So you primary table would contain the key fields of "last name", "first name", "middle initial", and maybe even "street", if that is needed to distinquish the differences between duplicate-like names.

          Those same "key fields" are duplicated on a "sub-table" so you can track repeated information. For example, the primary data table contains all the important particulars of the customer. And that may be good enough for what you want to do.... BUT, if you also want to keep track of repeated service calls to that customer, that single data table would be somewhat limited.

          So, in a case where you want to have "repeated" data entry like several service calls, you create a second data table called "Service" (for example), in which you build a table starting with the "key" fields from the first table, and then you add the fields particular to the service call itself: like "date", "time", "charge", "service required"... that latter you may wish to break down into whatever number of catagories that may apply.

          This kind of data table set up is referred to as a "one-to-many" data design... in other words, you have one master data table (holding the primary customer information), but also a table to hold the "many" other entry data for things like service calls. The two tables are linked together by their key fields and once designed are transparent when you later make your entry form or report forms.

          For example, I collect the data on an test candidate when he takes his first exam. I have his name, ID number, address, phone, etc. on a master table. I then have a secondary table which has his name and ID number (that's makes him unique) as a link, but also has the exam date, exam ID, score, and three examiners ID's. That way, I can pull up the guys record and at the same time see (by link to the sub-table) all of the exam ID that he may have.

          That kind of "one-to-many" relationship is usually necessary, because you don't want to build the "many" data fields into the main data table... simply because you can't efficiently sort across multiple fields for report data in one table. Some customers may only have on service entry, and others may have dozens of time.

          I know it may sound confusing, but it is really fairly simple. The complexity is only in the number of customers and the number of calls that are made over time.

          The other good thing about databases are that you can go back and change field criteria. If a field is too short, it can easily be expanded. Names can be changed, as well as sub-table data and links. Entry forms and reports are also easily changed to fit your needs too.