djblois - 6 months ago 28

SQL Question

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
```