1

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
Erikas
  • 13
  • 3
  • What was the "oracle" tag doing below your question? Please monitor the tags at the bottom of your post - this web site adds some automatically, and often they are the wrong ones. Edit as needed, preferably before you post your question. –  Nov 29 '21 at 15:08
  • So field `Nr` is of data type Decimal? Or how do you imagine this? – Gustav Nov 29 '21 at 15:43
  • 1
    You want to calculate a row sequence number for each group. For SQL solution, need a unique record identifier - autonumber should serve https://stackoverflow.com/questions/70052620/access-query-counter-per-group/70052866#70052866. However, structuring query to ignore groups of 1 may not be feasible. Otherwise, use VBA procedure. – June7 Nov 29 '21 at 18:22
  • Field Nr is of type Short Text, I could change it to Number if needed. I have Primary number, but still have no idea how could I start numbering if the field above has the same value – Erikas Nov 30 '21 at 08:49
  • As already noted, need a unique record identifier field. Did you review the referenced link? – June7 Nov 30 '21 at 09:44

1 Answers1

0

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:

enter image description here

Gustav
  • 53,498
  • 7
  • 29
  • 55