Counting and Numbering Records in a Query

[Phil-AND]

Hey everyone! I have an access database that I want to auto number the records in a query, but restart the number sequence every time the last name changes. For instance here is the table:

Ben	Smith
Steven	Smith
Carol	Smith
Angie	Smith
Tom	Smith
Dave	Stevens
Alex	Stevens
Nate	Richards
Brain	Richards
David	Richards

Here is what I want the query to look like when I open it:

Ben	Smith	1
Steven	Smith	2
Carol	Smith	3
Angie	Smith	4
Tom	Smith	5
Dave	Stevens	1
Alex	Stevens	2
Nate	Richards	1
Brian	Richards	2
David	Richards	3

So basically I want the query to autonumber every time the last name is the same, but restart every time it changes. I am pulling my hair out trying to find code that will do this. Thanks!

[orange]

That is not an autonumber in Access. See this for more about autonumbers

Why exactly do you need these numbers? - they will be temporary.
Search Query with Running count.

[Phil-AND]

Basically I will be using these numbers to create a reference Identification Number for each record. At the end of the identification number, will be this number I am trying to generate. But it is based upon how many "Smiths" or "Richards" I have. I was thinking of generating this number and creating an append query to help create these reference ID's. I hope what I have said makes sense.

[Phil-AND]

So I have been looking up the query with running count, and this is much closer to what I am trying to achieve. I guess the best thread I have found is this one: https://www.accessforums.net/queries...ess-48111.html, but I dont completely understand what is going on in that thread. Is anyone able to help translate what they did to what I am trying to do?

[orange]

?? And when you add another Smith? And if you sort them differently?
Basic tenet of relational design--- 1 fact 1 field.
You can find the number of "Smith's" via query.
I'm not convinced you need the number, but we don't have many details of your requirements.
I think I'm missing something basic in your requirements.

Every record could have a numeric identifier, but why would they have to be sequential within Lastname?

[Phil-AND]

Thank you so much for your help! But actually I worked with a friend for hours on it and this is the code if anyone else runs into a similar issue:

SELECT Employees.[Last Name], Employees.[First Name], Employees.ID, DCount("Employees.[ID]","Employees"," Employees.[Last Name] = '" & Employees.[Last Name] & "' And ID <=" & [ID]) AS LastNameNum
FROM Employees
ORDER BY Employees.[Last Name];

But I did have to add one more column and I made it an auto number to give each record a unique identifier (Just as you had stated orange).

[orange]

Good stuff.

Every table in a relational database should have a Primary Key.!