Auto Repair Shop Database in Microsoft Access

Core Tables

The Repair Order Workflow

• Check-in

  Search by plate, VIN or phone. Show the last five ROs and any declined recommendations.

• Inspection and estimate

  Add labour and part lines with Approved left blank.

• Authorisation

  Record which lines the customer approved or declined, with time and method.

• Work

  Technicians clock on and off the RO; parts are issued from stock.

• Close and invoice

  Totals, tax, payment and the next service reminder set automatically.

Key Queries

Technician efficiency for the week

SELECT t.Name,
       Sum(l.FlagHours) AS Flagged,
       (SELECT Sum(DateDiff("n", c.StartTime, c.EndTime)) / 60
        FROM tblClockings AS c
        WHERE c.TechID = t.TechID
          AND c.StartTime >= Date() - Weekday(Date(), 2) + 1) AS Clocked,
       Sum(l.FlagHours) / Nz((SELECT Sum(DateDiff("n", c2.StartTime, c2.EndTime)) / 60
        FROM tblClockings AS c2 WHERE c2.TechID = t.TechID
          AND c2.StartTime >= Date() - Weekday(Date(), 2) + 1), 1) AS Efficiency
FROM (tblTechnicians AS t
      INNER JOIN tblROLines AS l ON t.TechID = l.TechID)
      INNER JOIN tblROs AS r ON l.ROID = r.ROID
WHERE r.ClosedOn >= Date() - Weekday(Date(), 2) + 1 AND l.LineType = "Labour"
GROUP BY t.TechID, t.Name;

Parts margin by month

SELECT Format(r.ClosedOn, "yyyy-mm") AS Month,
       Sum(l.Qty * l.Price) AS PartsSales,
       Sum(l.Qty * l.Cost) AS PartsCost,
       1 - Sum(l.Qty * l.Cost) / Sum(l.Qty * l.Price) AS PartsMargin
FROM tblROLines AS l INNER JOIN tblROs AS r ON l.ROID = r.ROID
WHERE l.LineType = "Part" AND l.Approved = True AND r.Status = "Closed"
GROUP BY Format(r.ClosedOn, "yyyy-mm");

Vehicles due for service

SELECT c.Name, c.Phone, c.Email, v.Year, v.Make, v.Model, v.Plate,
       v.NextServiceDate, v.NextServiceMileage
FROM tblVehicles AS v INNER JOIN tblCustomers AS c ON v.CustomerID = c.CustomerID
WHERE v.NextServiceDate Between Date() And Date() + 30
ORDER BY v.NextServiceDate;

Shop Dashboard

Where AI Helps at the Counter

• Turn technician shorthand ('LF pads 3mm rotors scored rec repl') into a clear sentence for the customer.

• Summarise a vehicle's history before the appointment: last visits, declined items, open warranties.

• Structure customer complaints from phone notes into a concern statement for the technician.

• Draft follow-up messages for declined work, mentioning the specific recommendation.

Build Notes for a Shop Network

• Split the database: back end on a reliable PC or small server, front end on each counter and bay PC.

• Wired connections, not Wi-Fi, for PCs that write to the database; see our guide on corruption over networks.

• Barcode scanning for parts issue speeds up the bay and keeps stock accurate.

• Invoice and payment export to your accounting software instead of retyping.