Access number identical fields with decimal number-CodePudding

I have a Table in Access and some field values are identical. If the field values are identical I want to have them numbered with decimal numbers. Is there a way to do this with SQL?

Below is an example of my table and what I want to do.

   Nr        Model     ID
411412315   Stalas     1
411412315   Stalas     2
411412315   Stalas     3
411412315   Stalas     4
411412316   Stalas     5
411412399   Stalas     6
411412311   Stalas     7
411412324   Stalas     8
411412324   Stalas     9

    Nr       Model     ID
411412315.1 Stalas     1
411412315.2 Stalas     2
411412315.3 Stalas     3
411412315.4 Stalas     4
411412316   Stalas     5
411412399   Stalas     6
411412311   Stalas     7
411412324.1 Stalas     8
411412324.2 Stalas     9

CodePudding user response：

You can use a query having two subqueries:

SELECT 
    [Nr] & IIf(
        (Select Count(*) From ModelNr As T Where T.Nr = ModelNr.Nr) > 1,
        "." & (Select Count(*) From ModelNr As T Where T.Nr = ModelNr.Nr And T.Id <= ModelNr.Id)) AS FullNr, 
    ModelNr.Id
FROM 
    ModelNr
ORDER BY 
    ModelNr.Id;

Output: