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;

 

[Pat Hartman]

Assuming you want the sequence numbers to be permanent, here is a sample database that shows how to create custom sequence numbers AND for list type numbers, renumber the items if you want to reorder the list.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk