Recordset to update a table with sequential numbers based on group of OrderID (1 Viewer)

dark11984

Registered User.
Local time
Today, 14:16
Joined
Mar 3, 2008
Messages
129
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.

1709012224280.png


Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:16
Joined
May 7, 2009
Messages
19,249
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

Super Moderator
Staff member
Local time
Today, 00:16
Joined
Feb 19, 2002
Messages
43,720
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.

 

Users who are viewing this thread

Top Bottom