CODEWITHSUNDEEP
sqlms-accessms-access-2010
BlueBerry
BlueBerry

Reputation: 397

Rename Duplicates in Access with SQL

I have a table in Access, and there is one column of data that has duplicates. I need to rename these duplicates (preferably by appending a 1, 2, 3, etc. at the end). For example, assume my data table looks like so:

ID     Name               Title
1      George Washington  PRES
2      Martha Washington  FL
3      John Adams         PRES
4      Thomas Jefferson   PRES
5      Benjamin Franklin  NA

I want to make it look like:

ID     Name               Title
1      George Washington  PRES-1
2      Martha Washington  FL-1
3      John Adams         PRES-2
4      Thomas Jefferson   PRES-3
5      Benjamin Franklin  NA-1

I wish to accomplish with an SQL Query in Access, but I'm open to other options. I just need to stay within Access. I'm very new to SQL, but open to learning anything!

Upvotes: 1

Views: 382

Answers (2)

HansUp
HansUp

Reputation: 97101

Access UPDATE queries which incorporate a subquery can trigger error #3073 ("Operation must use an updatable query").

When that happens, you can try a "domain function" instead of the subquery to get an updatable query. In your case, DCount is the appropriate domain function.

I tested this query with your sample data in Access 2010 and it did what you requested:

UPDATE YourTable AS y
SET y.Title =
    [y].[Title]
    & '-'
    & DCount("*", "YourTable", "[Title]='" & [y].[Title] & "' AND ID <= " & [y].[ID]);

Note you must replace YourTable with your table name in two places in that query.

Also note the basic concept is similar to Gordon's answer. But it's adapted for Access.

However be aware that DCount and the other Access domain functions (DSum, DMin, DMax, DAvg, etc.) are not portable to other databases.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In Access, I think you can do this with a correlated subquery in an update:

update table as t
    set title = title & '-' & (select count(*)
                               from table as t2
                               where t2.title = t.title and t2.id <= t.id
                              );

Upvotes: 1

Related Questions