• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 834
  • Last Modified:

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.
0
Scamquist
Asked:
Scamquist
2 Solutions
 
Rey Obrero (Capricorn1)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
 
peter57rCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now