Equipment Rental Tracking in Microsoft Access

The Tables

Availability: The Overlap Rule

qryAvailableAssets (parameters: ProductID, ReqStart, ReqEnd):
SELECT a.AssetID, a.SerialNo
FROM tblAssets AS a
WHERE a.ProductID = [ReqProduct]
  AND a.Status = "Available"
  AND NOT EXISTS (
      SELECT 1 FROM tblContractLines AS l
      WHERE l.AssetID = a.AssetID
        AND l.ReturnedOn Is Null
        AND l.StartDate < [ReqEnd]
        AND l.DueBack > [ReqStart]);

Calculating the Charge

Public Function RentalCharge(ByVal productID As Long, _
                             ByVal startDate As Date, ByVal endDate As Date) As Currency
    Dim dayR As Currency, weekR As Currency, monthR As Currency
    Dim days As Long, months As Long, weeks As Long, rest As Long
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT DayRate, WeekRate, MonthRate " & _
             "FROM tblProducts WHERE ProductID = " & productID)
    dayR = rs!DayRate: weekR = rs!WeekRate: monthR = rs!MonthRate
    rs.Close

    days = DateDiff("d", startDate, endDate)
    If days < 1 Then days = 1

    months = days \ 28: rest = days Mod 28
    weeks = rest \ 7:   rest = rest Mod 7

    ' Each partial block is charged at the cheaper of its day total or the next block up
    RentalCharge = months * monthR _
                 + Min3(weeks * weekR + rest * dayR, (weeks + 1) * weekR, monthR)
End Function

Private Function Min3(ByVal a As Currency, ByVal b As Currency, ByVal c As Currency) As Currency
    Min3 = a
    If b < Min3 Then Min3 = b
    If c < Min3 Then Min3 = c
End Function

Check-Out and Return

• Check-out form: scan or select serial numbers, confirm ID and deposit, print the contract.

• Return form: scan the unit, record condition and hours meter, calculate final charge from actual return date.

• Late return: charge from the rate function automatically; flag on the contract.

• Failed inspection: set asset status to Maintenance and create a work order.

Daily Operations Views

Utilisation and Fleet Decisions

qryUtilisationByProduct (last 365 days):
SELECT p.Name, Count(a.AssetID) AS Units,
       Sum(Nz(DSum("DateDiff('d', [StartDate], Nz([ReturnedOn], Date()))",
                   "tblContractLines",
                   "AssetID=" & a.AssetID & " AND StartDate >= Date()-365"), 0)) AS DaysOnRent,
       Sum(Nz(DSum("Charge", "tblContractLines",
                   "AssetID=" & a.AssetID & " AND StartDate >= Date()-365"), 0)) AS Revenue,
       Sum(a.Cost) AS FleetCost
FROM tblProducts AS p INNER JOIN tblAssets AS a ON p.ProductID = a.ProductID
WHERE a.Status <> "Retired"
GROUP BY p.Name;

Time utilisation = DaysOnRent / (Units * 365)
Financial utilisation = Revenue / FleetCost

Where AI Helps

• Summarising return inspection notes into damage descriptions for the customer.

• Drafting overdue reminders with the contract details.

• Reading photographed delivery and collection notes into the contract.

• Suggesting fleet changes from utilisation and lost-booking data for the owner to review.