How to assign a number to each unique result?

[MsAxes]

I have a table with names. That table sometimes has duplicates and names drop or are added depending on the month. I would like to have a table that has the unique names with a number assigned to them starting with 1. I created query1 that appends to Names_IDs table which has a NamesC field with an auto-number assigned to each unique name. It works great except each time I run it, the auto-number starts from the number it left off. I just need it to reset back to 1 and it doesn't matter who has what number assigned.

Any ideas of how to have those numbers start with 1 each time the query is ran?

[Micron]

If you don't care that Mike is #2 today and #4 next time, don't bother with an autonumber, which should never be used as meaningful data - only to uniquely identify a record.
Search on 'ms access sql generate sequential numbers' and you'll get some hits. Some will be as simple as generating numbers, some will offer grouping. Pick one that applies to you.

[MsAxes]

Thank you - the statement below is what I used. This works perfectly when your sequential number is based on a text field.

SELECT     (Select Count(1) FROM Names_IDs as A        WHERE A.Names <=Names_IDs.Names) AS Sequence,
    Names_IDs.Names
FROM Names_IDs
ORDER BY Names_IDs.Names;

Credit to:

http://rogersaccessblog.blogspot.com...in-access.html

[davegri]

Never mind, OP found solution above.

[MsAxes]

I’d still like to read your suggestion

[davegri]

I’d still like to read your suggestion

OK, I added a RunCode to the end of Macro1, and added a code module to hold function fcnCount()

Here's the DB

MsAxes-davegri-v01.zip

[MsAxes]

This is a great alternative - thanks for sharing.

[davegri]

This is a great alternative - thanks for sharing.

Happy you like it, and thanks for the star!