Access number identical fields with decimal number

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

​x
 
   Nr        Model     ID411412315   Stalas     1411412315   Stalas     2411412315   Stalas     3411412315   Stalas     4411412316   Stalas     5411412399   Stalas     6411412311   Stalas     7411412324   Stalas     8411412324   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

 
    Nr       Model     ID411412315.1 Stalas     1411412315.2 Stalas     2411412315.3 Stalas     3411412315.4 Stalas     4411412316   Stalas     5411412399   Stalas     6411412311   Stalas     7411412324.1 Stalas     8411412324.2 Stalas     9​​    ​

Answer

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;

 
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.IdFROM     ModelNrORDER BY     ModelNr.Id;​

Output:

Advertisement

Answer