Query: Add 'Subset' counter to records

[JangLang]

In an Access 2010, I have a query grabbing records from an Invoice table. For each Invoice record, I want to begin a counter at '1' again. It would look like this (Invoice Num, Invoice counter num):

987789,1
987789,2
987789,3
456654,1
456654,2


123321,1
947343,1

Any ideas for building a statement in my query to make this happen?

[ItsMe]

Have you considered using a totals query? You can get a count using the count method within the totals row of the query's design view.

It seems as though you are interested in storing a value that is dynamic (always changing) inside a table. Why not check in periodically and view what the count of a specific invoice record is within your "Activity" table is. Whatever that table may be or do.

Otherwise, you will have to build out a separate log table and insert your invoice's ID number into that table. You will need another data field to hold the ever increasing count of unique Id numbers. The auto number Primary Key in this table will have to remain an independent column of fields.