Expert guide

HVAC Maintenance Agreement Tracker

Every paid visit scheduled, every renewal on time, every worn-out unit turned into a quote

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: store customers, sites, equipment, agreements and visits as related tables (Access is ideal), record visit frequency on each agreement, and run a seasonal routine that creates due visits for every active agreement. Track visit completion, renewal dates 60 days ahead, and equipment with repeated issues as replacement leads. Our work order management Access database is a ready starting point.

Maintenance agreements are the most valuable revenue an HVAC company has: predictable, paid in advance, and the main source of repair and replacement work. They are also easy to mismanage. Spring tune-ups get booked for whoever calls first, autumn visits for some customers never happen, and renewals lapse because nobody noticed the date. Customers who paid for two visits and got one do not renew.

Why a Spreadsheet Struggles Here

One customer can have several sites; one site can have several units; one agreement covers some of those units and includes two visits a year; each visit has a technician, date and findings. A single spreadsheet row per customer cannot hold that without duplicate rows or dozens of columns. For a small book Excel can cope, but the structure below in Access scales to thousands of agreements.

The Tables

TableKey fields
tblCustomersCustomerID, Name, BillingAddress, Phone, Email
tblSitesSiteID, CustomerID, Address, AccessNotes
tblEquipmentEquipID, SiteID, Type, Make, Model, Serial, InstallYear, Refrigerant
tblAgreementsAgreementID, CustomerID, Plan, StartDate, EndDate, Price, VisitsPerYear, Season pattern, AutoRenew, Status
tblAgreementEquipmentAgreementID, EquipID (which units are covered)
tblVisitsVisitID, AgreementID, SiteID, Season, DueFrom, DueBy, ScheduledOn, Technician, CompletedOn, Findings
tblFindingsFindingID, VisitID, EquipID, Issue, Severity, RecommendedAction, Quoted

Generating Seasonal Visits Automatically

-- Create spring visits for active agreements that include spring
-- and do not yet have one for this year (run in February)
INSERT INTO tblVisits (AgreementID, SiteID, Season, DueFrom, DueBy)
SELECT a.AgreementID, s.SiteID, "Spring 2027",
       #2027-03-01#, #2027-05-31#
FROM (tblAgreements AS a
      INNER JOIN tblSites AS s ON a.CustomerID = s.CustomerID)
WHERE a.Status = "Active"
  AND a.EndDate >= #2027-03-01#
  AND a.SeasonPattern In ("Spring+Autumn", "Spring")
  AND NOT EXISTS (SELECT 1 FROM tblVisits AS v
                  WHERE v.AgreementID = a.AgreementID
                    AND v.SiteID = s.SiteID
                    AND v.Season = "Spring 2027");

The NOT EXISTS clause makes the routine safe to run twice. In practice, wrap this in a form where the office manager picks the season and dates, so nobody edits SQL each year.

Dispatch: Working the Due List

  • Unscheduled visits for the season, sorted by area or postcode for efficient routing.

  • Customers with access notes or special requirements highlighted.

  • A button to create the work order from the visit, carrying site and equipment details.

  • Overdue visits (past DueBy and not completed) on a red list reviewed weekly.

If you already use a work order system, visits become work orders of type "PM". The work order management database shows that pattern.

Renewal Pipeline

qryRenewalsDue:
SELECT a.AgreementID, c.Name, a.Plan, a.EndDate, a.Price,
       DateDiff("d", Date(), a.EndDate) AS DaysLeft,
       (SELECT Count(*) FROM tblVisits v WHERE v.AgreementID = a.AgreementID
          AND v.CompletedOn Is Not Null) AS VisitsDone,
       a.VisitsPerYear
FROM tblAgreements AS a INNER JOIN tblCustomers AS c ON a.CustomerID = c.CustomerID
WHERE a.Status = "Active" AND a.EndDate Between Date() And Date() + 60
ORDER BY a.EndDate;

Showing VisitsDone next to VisitsPerYear before calling a customer about renewal is important. If a visit was missed, schedule it or offer a credit first; asking for renewal money from a customer who did not get their visit is how agreements are lost.

Turning Findings Into Replacement Leads

  • Record findings per unit with severity, not just in free-text notes.

  • Flag units older than a set age with repeated findings as replacement candidates.

  • Track whether each recommended action was quoted, and the outcome.

  • Report monthly: replacement candidates by technician and area for the sales team.

SELECT e.EquipID, c.Name, e.Make, e.Model, e.InstallYear,
       Count(f.FindingID) AS IssuesLast2Years
FROM (((tblFindings AS f
       INNER JOIN tblVisits AS v ON f.VisitID = v.VisitID)
       INNER JOIN tblEquipment AS e ON f.EquipID = e.EquipID)
       INNER JOIN tblSites AS s ON e.SiteID = s.SiteID)
       INNER JOIN tblCustomers AS c ON s.CustomerID = c.CustomerID
WHERE f.Severity In ("Major", "Critical")
  AND v.CompletedOn >= DateAdd("yyyy", -2, Date())
GROUP BY e.EquipID, c.Name, e.Make, e.Model, e.InstallYear
HAVING Count(f.FindingID) >= 2
ORDER BY e.InstallYear;

Where AI Helps

  • Turn technician shorthand into a clear visit summary for the customer.

  • Classify free-text findings into issue type and severity for reporting.

  • Draft renewal reminders that mention the visits completed and any recommendations.

  • Summarise a unit's service history before a replacement conversation.

The integration pattern is in connecting MS Access to the OpenAI API. Technicians' paper job sheets can be captured with AI document scanning into Access.

Billing and Accounting

Agreement invoices, renewals and repair work usually end up in QuickBooks. Syncing customers and invoices directly avoids retyping; see Access QuickBooks integration. For fleet and van maintenance alongside customer equipment, see the fleet maintenance log.

FAQ

HVAC maintenance agreement tracking FAQ

Visits, renewals and replacement leads

It is a system that records every customer service agreement, the equipment covered, the number of visits included per year, when each visit is due and completed, the agreement's renewal date and its revenue. It makes sure every visit customers paid for is scheduled, and that agreements are renewed before they lapse.

Excel works for a small book of agreements managed by one person. Once you have several hundred agreements, multiple technicians and a need to generate visits automatically, an Access database is more reliable, because agreements, sites, equipment and visits are separate related records.

Store the visit frequency on each agreement, such as spring and autumn, then run a routine that creates visit records for the coming season for every active agreement that does not already have one. Dispatchers then book those visits rather than remembering which customers are due.

Track the share of agreements up for renewal in a period that renewed, by count and by revenue, and separately the share of customers whose included visits were all completed. Agreements where visits were missed renew less often, so visit completion is the leading indicator.

AI can turn technician notes into clear visit summaries for customers, flag repeated issues on the same equipment that suggest a repair or replacement quote, and draft renewal reminders. Pricing and contract terms stay with your office.