Access Formula to Auto-Number Rows by 4's

I have seen variations of my question answered here:

• https://stackoverflow.com/questions/70052620/access-query-counter-per-group

• https://stackoverflow.com/questions/70388274/how-do-i-make-it-so-that-the-row-number-resets-whenever-a-certain-field-changes

But none of them show how to calculate row numbers in groups of X number (e.g. 4's), which should have no relation to anything in the data itself. So what formula can be entered that will result in this:

Notice specifically how the Crate Number increments UP by 1 every 4 rows? It matters NOT the sort of the rows. Use Case: I need to distribute computer equipment that fits in boxes that only fits four devices per box, so I need to identify what crate each device should go in for delivery.

I've tried the VB Script and downloaded the sample Access Files and tried modifying the following column formulas in Access queries but haven't found the combo I need:

(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value
        or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum

(Select Count(*) 
        From YourTable As T 
        Where T.ID < YourTable.Id) Mod 11 + 1

I'm not understanding what the formula do I supposed to even make it do what I need in my situation. Anyone have the answer at the top of mind?

To get the desired "Crate" value, you need assign table rows with unique numbers (rn).
Then Crate=(rn/4)+1 (integer divide). In Access VBA Crate=(rn\4)+1.

Usually, a function like "row_number()over(...)" is used for this.
MS Access does not support such a function and the calculation is performed using formulas similar to the ones you provided.
However, this formula implements another standard SQL function, "rank()over(...)".
There is no difference between them ("row_number()" and "rank()") if the table rows are unique within the columns being counted. See example in fiddle

Let's look at the calculation formula you provided using your data as an example. I added one line to the example to better show the calculation features.

    ('789541', 'Simba Limba', '4', '3') -- existing row  
    ('789541', 'Simba Limba', '5', '3') -- new row added to sample

1.First case

  (Select Count(*) From tableName As T2 Where T2.ID < T.ID)+1 formulaNum1

Because Id is not unique, all rows with Id=789541 take num=6.

2.Next case

(Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
   )+1 formulaNum2

For columns(ID,Name) values (789541,Simba Limba) is not unique, all rows with Id=789541,Name=Simba Limba take num=8.

3.Last case

(Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
            or(T2.ID = T.ID and T2.Name=t.Name and T2.Grade<t.Grade)
   )+1 formulaNum3

All rows have unique values for columns set (ID,Name,Grade) 4. Finally, calculate newCrate

  ,((Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
            or(T2.ID = T.ID and T2.Name=t.Name and T2.Grade<t.Grade)
   )+0)/4+1 newCratefrom tableName t

(change "/" to "\" in VBA)

Query

select * 
  ,(Select Count(*) From tableName As T2 
        Where T2.ID < T.ID)+1 formulaNum1
  ,(Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
   )+1 formulaNum2
  ,(Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
            or(T2.ID = T.ID and T2.Name=t.Name and T2.Grade<t.Grade)
   )+1 formulaNum3
  ,((Select Count(*) From tableName As T2 
        Where (T2.ID < T.ID) 
            or(T2.ID = T.ID and T2.Name<t.Name)
            or(T2.ID = T.ID and T2.Name=t.Name and T2.Grade<t.Grade)
   )+0)/4+1 newCratefrom tableName t
order by id,Name,Grade;

fiddle

Part 2
Another simple way to set row numbers is using public function with static variable like

Option Compare Database
Option Explicit

Public Function GetSeqNum(Optional NewValue As Variant) As Long
Static currentSeq As Long
    If Not IsNull(NewValue) Then
        currentSeq = currentSeq + 1
    Else
        currentSeq = 0
    End If
    GetSeqNum = currentSeq
End Function

This function defined in module.

Use this function as

SELECT T.*,(GetSeqNum(Id)\4+1) as Crate
FROM yourTable as T

To reset sequence, call

  GetSeqNum(null)

from VBA.