Access Rank / Update Duplicates

Question

I have two columns in my output: Name of a person and associated ID number of this person. It can be that one name has assigned multiple IDs (while the IDs are always unique) - given that I need unique values for the names to upload somewhere, I would like to update the names with multiple IDs to be displayed as "Name.", "Name.." and so on.

I already made the query which shows purely the duplicated names with the unique IDs.

The amount of name duplicates differs from person to person.

Example-

Name	ID
A	1
A	2
A	3
B	4
B	5
B	6
B	7
C	8
C	9

And I want this to be:

Name	ID
A	1
A.	2
A..	3
B	4
B.	5
B..	6
B...	7
C	8
C.	9

Does anybody know an elegant way to do this?

Many thanks, Daniela

Answer 1

We have string function to repeat a character

SELECT personName & STRING((SELECT COUNT(id) from table1 AS t1 
      WHERE table1.id >t1.id AND table1.personName = t1.personName),".") as [Name], ID
FROM table1