Manipulate duplicated records In a table ( in Microsoft Access), I have a large number of records that have duplicated field, for example field-1. I have to change the table and set field-1 as primary key. But as you know, it is not possible. So I want to change the value of field-1 for duplicated data. For example, now I have a table like this:

field-1  | field-2
1        | f1 
1        | f2
2        | f3 
2        | f4


And want to change to this:

field-1  | field-2
1        | f1 
201      | f2
2        | f3 
202      | f4


Note:

Maximum number of duplicated record is 2.

How I can do that?
This is kind of complicated, but you could try this.

Create a duplicate table to store the duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 2


Create a nonDuplicate table to store the non duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 1


Create a duplicateValue table to store the modified values of the duplicates. You will need to modify the case statement below to work with Access, before using this query in an insert statement. Here are some links that should help you.

How to add row number in Access

Access case statements

--Modify the 2nd instance of the duplicate field
--If the row number is Odd, then the value is the same
--If the row number is Even, then 200 is added to the value
SELECT
  --This is how you would do it with SQL Server
  --You will need to figure out how to modify this code to work with Access
  CASE WHEN ROW_NUMBER() OVER (ORDER BY field-1 ASC) % 2 &lt;&gt; 0 
  THEN field-1 ELSE field-1 + 200 END AS 'field-1'
  --THE ABOVE CASE STATEMENT NEEDS TO BE MODIFIED FOR ACCESS
  ,field-2
FROM yourtable
    JOIN duplicateTable ON yourtable.field-1 = duplicateTable.field-1


Create a nonDuplicateValue table to store the non duplicate values. Use this query in your insert statement:

--Get the values of field 1 and field 2
--from non duplicate rows
SELECT field-1,field-2
FROM yourtable
    JOIN nonDuplicateTable ON yourtable.field-1 = nonDuplicateTable.field-1


Query to union the results together and to use for the insert into the final table

SELECT field-1,field-2
FROM duplicateValues

UNION

SELECT field-1,field-2
FROM nonDuplicateValues