How to replace value in a table with value from another table ?

[summer23]

my ID client table is like this:

Client - ID - other infomation
ABC - 20016 - x
CDE - 20015 - y
EFG - 20017 - z
QWE - 10004 - g

this is the DATA in the "temp table" that I import from excel, and excel is downloaded from company's system, this excel's data has old ID number (start with 2 from the left) mix with new type (start with 1 from the left). I use an append query to update it to the "ID client table", and I have to use name of client as brigde to change ID from old to new, (it'll not correct if name of client is wrong, duplicate, etc..)

and I have a table that have both ID old and ID new on it like this: (table 2)

Client - ID old - ID new
ABC - 20016 - 10001
CDE - 20015 - 10002
EFG - 20017 - 10003
QWE - 20014 - 10004
my question is
1- I want to import the client DATA in the excel file directly to "client ID table" (I already have code to do that - Docmd.tran stuff), then, I want to use an query, macro, or VBA function to change any ID of client in "client data table" that is not start with number 1 from the left (ex: ABC : 20016 -> 10001) and still keep the ID if it start with 1 (ex: QWE : 10004 -> do nothing). How to change like that ?
I've tested with update query, but it did not change anything,
code like this

UPDATE 
Client DATA table INNER JOIN [ID] 
ON [client DATA table].[ID] = [table 2].[ID old] 
SET [table 2].[ID new] = [Client DATA table].[ID];

even if I change the input field to update to this

iif(left([client data table].[ID],1)=2, 
[table 2].[ID new], [client data table].[ID])

I need to use information in table 2 to replace old ID with new ID to the "client data table", not vise versa

I can replace those ID in Excel like this:
Name | ID(originalImport) | ID(afterReplace)
abc | 20016 | =if(left("ID(originalImport)",1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImport)")
cde | 20015 | =if(left("ID(originalImport)",1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImport)")
efg | 20017 | =if(left("ID(originalImport)",1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImport)")
qwe | 10004 | =if(left("ID(originalImport)",1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImport)")
Result in ID(afterReplace) will take from table 2 and showup.

but How to do that in Access, access using relationship between table to query and showup same as vlookup,but if that relationship is link to the one that we have to replace (ID), it never work.
how to do find and replace with criterial from another table in Access ?
I'm thinking about this code

Public Function FindReplace(FieldToFind As Variant, FieldToReplace As Variant) As String

If FieldToFind = FieldToReplace Then FindReplace = FieldToFind
    Else: FindReplace = FieldToReplace
 End If
End Function

but it show up error "circular reference cause by alias 'ID' in query definition's SELECT list" when change view to sheetview

any idea ?
please help me ((( I'm sinking.....

 

[Ranman256]

(using the rule to never destroy data)
in your temp table, create a new field NEWID.
using an update query , make a copy of ID to NEWID
then an update to revise NEWID based on your rules.
Now, import the data to the main table using NEWID.

 

[summer23]

(using the rule to never destroy data)
in your temp table, create a new field NEWID.
using an update query , make a copy of ID to NEWID
then an update to revise NEWID based on your rules.
Now, import the data to the main table using NEWID.

I know that step, and already did it, but when I lookup value from "table 2" with Vba code to update on new ID, it error "circular reference cause by alias 'ID' in query definition's SELECT list".
if i use Dlookup, it only return the first result and show that result replicate till the end of table...

and query SQL never make it, since ID is the relationship between 2 table.