Solved Add suffix to duplicate values in access query (1 Viewer)

Ihk

Member
Local time
Today, 22:46
Joined
Apr 7, 2020
Messages
280
Hi,
I want to add suffix to my all duplicate numbers. This suffix number should restart from each duplicate. Example below.
Example Values are::
ABC
ABC
ADF
ADF
GHR
GHR
GHR
want to generate it as
ABC_1
ABC_2
ABC_3
ADF_1
ADF-2
GHR_1
GHR_2
GHR_3

These duplicate values are different, can be less than 10 or can be more. Overall data has more than 3000 values.
How can append it in Query. Challenge is to restart suffix numbers on next value, Other wise I can merge it with ID autonumber, but it then wont restart from each next value.
Thanks
 

plog

Banishment Pending
Local time
Today, 16:46
Joined
May 11, 2011
Messages
11,612
To do this with just a query you need a way to uniquely identify each record. Do you have that? Is there an autonumber in the table? Possibly a timestamp for each record?

Once you do that, it simply becomes a "running sum query" and you can search the forum for that term.
 

Ihk

Member
Local time
Today, 22:46
Joined
Apr 7, 2020
Messages
280
To do this with just a query you need a way to uniquely identify each record. Do you have that? Is there an autonumber in the table? Possibly a timestamp for each record?

Once you do that, it simply becomes a "running sum query" and you can search the forum for that term.
Thank you for your reply. Yes this is unique record and had autonumber ID field, but not time stamp. This table in my DB is very simple containing just two fields "ID" as autonumber and the this record "Sess_rec"

I tried to add two fields, in query like (= Sess_rec & ID) but this method does not restart the number from beginning for each next record.
for Example I dont want
ABC_1
ABC_2
CDE_3
CDE_4
Rather I want
ABC_1
ABC_2
CDE_1
CDE_2
etc
Thank you
 

plog

Banishment Pending
Local time
Today, 16:46
Joined
May 11, 2011
Messages
11,612
Did you search the forum for running sum query?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:46
Joined
May 21, 2018
Messages
8,463
Table1 Table1

IDField1
1​
ABC
2​
HIJ
3​
DEF
4​
DEF
5​
ABC
6​
HIJ
7​
ABC
8​
DEF
9​
ABC
Code:
SELECT table1.field1,
       table1.id,
       [table1].[field1] & "_" & (SELECT Count(*) + 1
                                  FROM   table1 AS B
                                  WHERE  B.field1 = table1.field1
                                         AND table1.id > b.id) AS NewName
FROM   table1
ORDER  BY table1.field1,
          table1.id;
Query1 Query1

Field1IDNewName
ABC
1​
ABC_1
ABC
5​
ABC_2
ABC
7​
ABC_3
ABC
9​
ABC_4
DEF
3​
DEF_1
DEF
4​
DEF_2
DEF
8​
DEF_3
HIJ
2​
HIJ_1
HIJ
6​
HIJ_2

or with a DCOUNT
Code:
SELECT table1.field1,
       table1.id,
       [table1].[field1] & "_" & Dcount("*", "table1", "field1 = '" & [field1] &
                                                       "' and id <=" & [id]) AS
       NewName
FROM   table1
ORDER  BY table1.field1,
          table1.i
 
Last edited:
  • Love
Reactions: Ihk

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2002
Messages
42,976
Adding a suffix of indeterminate length to a text field of indeterminant length is generally not useful. Better to add an entire new field to hold the sequence number. That way the text field can be text and the suffix can be numeric.
 

Ihk

Member
Local time
Today, 22:46
Joined
Apr 7, 2020
Messages
280
Table1 Table1

IDField1
1​
ABC
2​
HIJ
3​
DEF
4​
DEF
5​
ABC
6​
HIJ
7​
ABC
8​
DEF
9​
ABC
Code:
SELECT table1.field1,
       table1.id,
       [table1].[field1] & "_" & (SELECT Count(*) + 1
                                  FROM   table1 AS B
                                  WHERE  B.field1 = table1.field1
                                         AND table1.id > b.id) AS NewName
FROM   table1
ORDER  BY table1.field1,
          table1.id;
Query1 Query1

Field1IDNewName
ABC
1​
ABC_1
ABC
5​
ABC_2
ABC
7​
ABC_3
ABC
9​
ABC_4
DEF
3​
DEF_1
DEF
4​
DEF_2
DEF
8​
DEF_3
HIJ
2​
HIJ_1
HIJ
6​
HIJ_2

or with a DCOUNT
Code:
SELECT table1.field1,
       table1.id,
       [table1].[field1] & "_" & Dcount("*", "table1", "field1 = '" & [field1] &
                                                       "' and id <=" & [id]) AS
       NewName
FROM   table1
ORDER  BY table1.field1,
          table1.i
Dear @MajP , Thank you very much. This is the exact solution what I was looking for. Best regards.
 

Ihk

Member
Local time
Today, 22:46
Joined
Apr 7, 2020
Messages
280
Adding a suffix of indeterminate length to a text field of indeterminant length is generally not useful. Better to add an entire new field to hold the sequence number. That way the text field can be text and the suffix can be numeric.
This is nice suggestion. But in this case I wanted to give different name to my duplicate record with sequential number (starting from 1 for each).
 

Users who are viewing this thread

Top Bottom