Autonumbering Multiple Orders together

[meagain]

Hi All,

I need a means to provide unique sequential record numbers (starting at 01) for approximately 200 orders, where the number of records per order will vary, but must always start at 01 for each order.

In the past, these orders were processed individually and flowed through a table with an autonumbering field which was recreated prior to an order flowing thru to ensure the first record was always numbered 01. Now I need to handle about 200 of these puppies at the same time. How can I get the records to show with sequential numbers starting with 01 for each order?

ie:
Order A has 5 reords
Order B has 3 records
Order C has 7 records

Assuming the auto-number field starts at 01, if I run it thru the table with the auto-numbering field at the same time I will get continuous record #'s. Here is an example of what I have vs what I need;

Current Required
Order # Record # Record #
A 01 01
A 02 02
A 03 03
A 04 04
A 05 05
B 06 01
B 07 02
B 08 03
C 09 01
C 10 02
C 11 03
C 12 04
C 13 05
C 14 06
C 15 07

Your wisdom is greatly appreciated.
Thanks.
ps: sorry for the mish-mash in the table above, I haven't mastered formatting in these posts yet

 

[MajP]

You do not have to save this value just calculate it in a query. Ex:

SELECT 
 tblOrders.Order, 
 tblOrders.CurrentID, 
 (Select count(A.Order)+1 
  from 
   tblOrders as A 
where 
 tblOrders.currentID > A.currentID AND tblOrders.Order = a.order) AS OrderRecord
FROM 
 tblOrders;

Produces:

Order CurrentID OrderRecord
A	1	1
A	2	2
A	3	3
A	4	4
A	5	5
B	6	1
B	7	2
B	8	3
C	9	1
C	10	2
C	11	3
C	12	4
C	13	5
C	14	6