Create Routes after 26 pallets

[lakers8175]

I have a table that has a pallet count column and a Sequence column. Can someone please help me create trailers but using decending sequence, with 26 pallets per trailer. Example, Below, the code should create a new route after 26 pallets or something close to 26.
Pallets PickSeq Route
3 114 1
6 113 1
13 112 1
5 111 1
3 110 Then start route 2
3 109
3 108
5 107
5 106
3 105
5 104


Any help would be appreciated.
Thanks

 

[Remou]

Something close to 26" is a little vague. Here is a sketch:

'Ref: Microsoft DAO x.x Object Library
Set rs = CurrentDb.OpenRecordset("Select Pallets, PickSeq, Route From tblTable Order By PickSeq DESC")

intC = 0
intR = 1
Do While Not rs.EOF()

    intC = intC + rs!Pallets
    
    rs.Edit
    If intC <= 26 Then
        rs!Route = intR
    Else
        intC = rs!Pallets
        intR = intR + 1
        rs!Route = intR
    End If
    rs.Update
    rs.MoveNext
Loop

 

[lakers8175]

I'm sorry, I would like the Pallest count to not exceed 26, if it picks the next line and it takes it over 26, then stop at the previous line for that route number. Do I do this in a Function?

 

[Remou]

The code I provided will stop before or at 26. It should work in a Sub after a few changes to match your table names etc.

 

[lakers8175]

That worked,THANK YOU!!!!!!

 

[lakers8175]

Sorry, One more question, I need to put a [stop] in the same table, it can be an autonumber starting at 01, it needs to be assigned starting with the highiest[PickSeq]number.

Example.

PickSeq Route Stop
1 1 10
2 1 09
3 1 08
4 1 07
5 1 06
6 1 05
7 1 04
8 1 03
9 1 02
10 1 01

 

[lakers8175]

The number needs to start over for each route.

 

[Remou]

Use the same idea and fiddle around a bit. Post back if you get stuck.

 

[lakers8175]

I am terrible at this type of coding, this is as close as I could get.
Sub Stopseq()
Set rs = CurrentDb.OpenRecordset("Select PickSeq, Route, Stop_ From Cube1111Seq ORDER BY Route, PickSeq DESC")

intC = 0
intR = 1
Do While Not rs.EOF()

intC = rs!Route

rs.Edit
If intC > 0 Then
rs!Stop_ = intR
Else
intC = rs!Route
intR = intR + 1
rs!Stop_ = intR
End If
rs.Update
rs.MoveNext
Loop
End Sub

 

[lakers8175]

OK, I'm closer. I just need the stop to start over, at each route number.
Sub Stopseq()
Set rs = CurrentDb.OpenRecordset("Select PickSeq, Route, Stop_ From Cube1111Seq ORDER BY Route, PickSeq DESC")

intC = 0
intR = 0
Do While Not rs.EOF()

intC = rs!Route

rs.Edit
If intC > 0 Then
intC = rs!Route
intR = intR + 1
rs!Stop_ = intR
Else
rs!Stop_ = intR
End If
rs.Update
rs.MoveNext
Loop
End Sub

 

[Remou]

Ok.

Sub Stopseq()
Set rs = CurrentDb.OpenRecordset("Select PickSeq, Route, Stop_ From Cube1111Seq ORDER BY Route, PickSeq DESC")

intR = 0
intC = rs!Route

Do While Not rs.EOF()
    rs.Edit
    If rs!Route = intC Then
        intR = intR + 1
        rs!Stop_ = intR
    Else
        intC = rs!Route
        intR = 1
        rs!Stop_ = intR
    End If
    rs.Update
    rs.MoveNext
Loop
End Sub

 

[lakers8175]

Thank you very much, I added code so it would start with a different route numbers depending in the day of week it is ran. Now I am set.

intR = IIf(Format(Date, "ddd") = "Sun", 601, IIf(Format(Date, "ddd") = "Mon", 701, IIf(Format(Date, "ddd") = "Tue", 801, IIf(Format(Date, "ddd") = "Wed", 901, IIf(Format(Date, "ddd") = "Thu", 950, IIf(Format(Date, "ddd") = "Fri", 950, IIf(Format(Date, "ddd") = "Sat", 6, "")))))))


Thank you, Thank you!!!!!!!