Renumbering Module (1 Viewer)

I have a table which has two fields with a stock code multiple times together with an operation number in ascending order for each of the lines. I then need to generate a new number in the 'Final Op' field incremented by 1 up to the last line for that stock code and then start again at 1 for the next stock code and so on. I have hundreds of stock codes so doing this manually is not an option plus I need to automate this so someone can run it again in the future.

I dont know how to write the code myself so I wondered if someone could write the code for me please. I would be extremely grateful.

For example
StockCode OperationNumber FinalOp
100000 201 1
100000 202 2
100000 203 3
100000 204 4
100000 205 5
100001 201 1
100001 202 2
100001 203 3
100001 204 4
100001 205 5
100001 206 6
100001 301 7
100002 201 1
100002 202 2
100002 203 3
100002 204 4
100002 205 5
100002 206 6
100002 301 7
100002 302 8


Thanks in advance.
Attached is the full table I have.

in sql you would have something like

not the use of aliases for the tables

Hi CJ_London

I have tried your suggestion but I am getting an error that I dont understand.
This is my SQL as my table is called OperationRenumber


The error I get is "Your query does not include the specified expression 'StockCode' as part of an aggregate function"

The table does have the field name of 'StockCode' so I dont understand why I am getting this error.

Attached is a screenshot of the query design in Access.

Once I get this query working I want to be able to change it into an update query (or a make table one) so that I have the 'FinalOp' with the new values.

Hope you can help

Ah sorry, forgot the GROUP BY

suspect you cannot use this as an update query - and there should be no need to store the value, just calculate as and when required.

CJ_London said:

Ah sorry, forgot the GROUP BY

Code:Copy to clipboard

SELECT A.StockCode, A.OperationNumber, Count(B.OperationNo) AS FinalOp
FROM OperationRenumber AS A INNER JOIN OperationRenumber AS B ON A.StockCode = B.stockcode
WHERE (((B.OperationNumber)<=[A].[OperationNumber]))
GROUP BY A.StockCode, A.OperationNumber;

Click to expand...

Thank you so much this works perfectly.

I had to make a slight adjustment to the field name OperationNo as it called OperationNumber but otherwise it works great.

Here is my final code

If you have a large number of records, ensure that StockCode and OperationNumber fields are both indexed, otherwise it will be pretty slow