Recordset to update a table with sequential numbers based on group of OrderID

[dark11984]

Hi, I am an amateur with Recordsets and looking for some advice.

I have a table that lists some order numbers. I need to create a recordset which will add sequential numbering to OrderLnID, 1,2,3,4, etc for each record then reset to 1 each time the order number changes, similar to below table.

Thanks.

 

[June7]

Review

http://allenbrowne.com/ranking.html

https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/

https://isladogs.co.uk/rank-order-queries/index.html

 

[arnelgp]

another suggestion, instead of OrderLnID field, add an Autonumber field and generated the Line number using a query:

orderDetail (table)

OrderID (Autonumber)
OrderNo (short string)

using Query (Query1):

Select OrderNo, (SELECT Count("1") From OrderDetail AS T
Where T.OrderNo = OrderDetail.OrderNo And T.OrderID <= OrderDetail.OrderID) As OrderLnID
From OrderDetail Order By OrderNo, OrderID;