create duplicate of record

[megatronixs]

Hi all,

I want to create a duplicate record of existing record and change the value from a field to a different value.
As example, if the country is Republic of Ireland, it should change it to Northern Ireland (in our database we have it as NI and ROI)
It should check the record what the field "Country" is NI and than add the new record with ROI, or the other way around.
Creating a duplicate would be easy, but I can't figure out how to do the last part in a query.

Any ideas in how to solve this?

Greetings.

 

[Minty]

If it only the one field and only a couple of values to compare then use an switch statement , there is information here https://access-programmers.co.uk/forums/showthread.php?t=173682

If it is a lot of different values build a table with the original code and the replacement code and simply use a join to bring in the replacement value.