djblois - 1 year ago 62
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).

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download