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
Advertisement
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;
Output: