djblois djblois - 1 month ago 9
SQL Question

Access query to find next maintenance cycle

I have a weird query. I am creating a vehicle maintenance database. Each vehicle has different maintenance cycles at 250, 500, 1000, and 2000. so when a user pulls up to a vehicle and enters the mileage it needs to know what is the next maintenance cycle.

Here is an example. so the vehicle would have 1150 miles the next cycle would be 1250. I have a table that says there are 250, 500, 1000, and 2000 cycles but I don't have anything for the 1250 or 1500 or 1750 because they are the same as 250... so it would need to pick the 250 maintenance cycle and then over 1250, it would need to show that 1500 is the next cycle and so on.

So what would I do to pick the next maintenance cycle?

Here is some data:

250 Cycle
500 Cycle
1000 Cycle
2000 Cycle


a user pulls up to a vehicle with 850 cycles, how do I pick the 1000 in this case but then also if a user pulls up to a vehicle with 1050, it would need to pick the 250 cycle (because there is no 1250 cycle).

Answer

In Access it is much simpler to create a function:

Public Function NextCycle(ByVal Mileage As Long) As Long

    Dim rst         As DAO.Recordset

    Dim SQL         As String
    Dim Cycle       As Long
    Dim ThisCycle   As Long
    Dim NextService As Long

    SQL = "Select Cycle From Cycles Order By 1"
    Set rst = CurrentDb.OpenRecordset(SQL)

    Cycle = rst!Cycle.Value
    While Not rst.EOF
        ThisCycle = rst!Cycle.Value
        NextService = -Int(-Mileage / ThisCycle) * ThisCycle
        If NextService - Mileage < Cycle Then
            Cycle = ThisCycle
        End If
        Debug.Print Mileage, ThisCycle, NextService, NextService - Mileage, Cycle
        rst.MoveNext
    Wend
    rst.Close

    Set rst = Nothing

    NextCycle = Cycle

End Function

Example results:

? NextCycle(850)
 850           250           1000          150           250 
 850           500           1000          150           500 
 850           1000          1000          150           1000 
 850           2000          2000          1150          1000 
 1000 

? NextCycle(1050)
 1050          250           1250          200           250 
 1050          500           1500          450           250 
 1050          1000          2000          950           250 
 1050          2000          2000          950           250 
 250 

? NextCycle(10850)
 10850         250           11000         150           250 
 10850         500           11000         150           500 
 10850         1000          11000         150           1000 
 10850         2000          12000         1150          1000 
 1000