Access Table - Add Field To Auto Number Records, Resetting When Record Value Changes

I have a table with a column of Item Numbers sorted by the item number.
I am tying to find a way to insert a column to number the records based on the item number.
For example,

Counter      Item Number
1                   AAA
2                   AAA
3                   AAA
4                   AAA
5                   AAA
1                   BBB
2                   BBB
3                   BBB
1                   CCC
2                   CCC
3                   CCC
4                   CCC
5                   CCC
etc.

Any help would be appreciated.
LVL 1
ScamquistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can do that in a query



select a.*, (select count(*) from tblData B where B.itemnumber=a.itemnumber and b.id >=a.id) as counter
from tblData as A
order by a.itemnumber
0
 
peter57rConnect With a Mentor Commented:
To do this, you must have an autonumber ID field already present in the table (or some other unique field which provides a defined sort sequence).  So if you don't have one you must add one.

Then a query such as the one posted by Cap1 will number like records in a query.
Unless you have a further reason for doing so, there is no real need to update the table with this sequence value.
0
 
ScamquistAuthor Commented:
Thank you both.  Capricorn1 had the right formula.  However, I did not have an ID field, so it was failing.  Added the ID (peter57r) and all was well with the world.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.