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

[hvsummer]

this is my situation, my organization have changed the customer's ID, but data raw from system still keep old value.
my question is how to change/edit/replace the value in the customer's ID table
my tables are like this:

Name | ID | detail
qwe | 20015 | ynu
abc | 20016 | nhf
xyz | 20017 | y5h

and the table content new ID is like this:

Name | ID new | ID old
abc | 10001 | 20015
xyz | 10002 | 20016
qwe | 10003 | 20017

what can I do to replace ID in customer's ID table ?
macro ? vba ? I can't use find and replace since I have too much ID to change.

please help me, thank you.

[jforbes]

You could write and Update Query against the Old table and replace the ID's with the New IDs.

[hvsummer]

the update seem not work correctly, it update nothing to the custumer's ID table..

[Rabbit]

You need to post your code so we can tell you what's wrong with it.

[NeoPa]

HVSummer:
the update seem not work correctly

Which update is that exactly? It's hard to discuss or help with your query if you don't even show us what you've tried.

Also, I think we must assume from the description supplied that the correlation between the [Name] and [ID] fields is supposed to be maintained from one set of data to the next, yet clearly the data you've supplied doesn't match. Can you confirm this is a mistake and not that we've misunderstood what it is you're trying to explain?

May I suggest, if you're to get any benefit from using these forums, that you'd be well advised to pay more attention to what you're writing before you post it. We have to be able to rely on what you say as it's all we have to deal with. If it's sloppy and unreliable then we can't easily help you and your time is wasted as well as ours.

[hvsummer]

ok, let's make it more detail,
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])

2- if the question 1 is impossible, then, is there anyother way to change any ID (that start with number 2 on left side) to the right ID (start with number 1) ?

sorry to say, but I'm noob when face the trouble like this, I can make data base, make relation ship, import with code, make function with code, but I'm weak on query (that mean i don't know anything bout SQL)
please help me more, thank.

[NeoPa]

For someone who's a complete n00b at SQL you got very close to the right answer.

The only problem I see with your SQL is in line #4 where you assign the value from the data you want to change to the data you want to use to update the field.

So, try as line #4 :

SET [Client DATA table].[ID] = [table 2].[ID new];

As this is probably the PK we're updating here I would probably recommend a different approach where the data is loaded into an intermediate table first and updated as it's transferred across, but that's necessary to make it work. What you had, with that minor modification, should work for you. If you want to explore better ways later then later is a good time to think about it.

PS. I just noticed a few other problems with your SQL so try this whole replacement instead :

UPDATE [Client DATA table] INNER JOIN [Table 2] 
ON [Client DATA table].[ID] = [Table 2].[ID Old] 
SET [Client DATA table].[ID] = [Table 2].[ID New];

[hvsummer]

Thank NeoPa, but the value return that I saw on the Data sheet view is all old ID, mean it'll replace new id with old one, and beside that,
your SQL's code is query update to [table 2] ? I have to update info from table 2 to "client data table", not use info on client data table to update table 2, table 2 is just info-to-correct-table...

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(originalImpo rt) | ID(afterReplace )
abc | 20016 | =if(left("ID(or iginalImport)", 1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImp ort)")
cde | 20015 | =if(left("ID(or iginalImport)", 1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImp ort)")
efg | 20017 | =if(left("ID(or iginalImport)", 1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImp ort)")
qwe | 10004 | =if(left("ID(or iginalImport)", 1)=2, Vlookup("Name", "Table 2!", 3, TRUE), "ID(originalImp ort)")
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.....

[NeoPa]

It seems that you're failing to grasp what the SQL means. Your original SQL was back-to-front. I changed it around for you to do exactly what it is you want it to do. Have you tried that, or did you assume my SQL was incorrect based on your own understanding of how it works?

If you can accept that your understanding was wrong and that maybe I know what you need then please try it out and report on what you find.

A discussion based on a difference of understanding is fairly pointless. Obviously you can choose how you proceed with this, but I can't help you unless you trust that I know what I'm saying. Ultimately, it's your choice what you do with any suggestions I make.

[hvsummer]

I know it NeoPa, still thank you, and let's me draw it on picture, the relationship between table ==

https://lh4.googleusercontent.com/yUZH1NHlPzDVG-Hmbkn1KBU6KfS9XFXq2T1AO4xXphbbQBlPJFFsI2M7wQnLi__PZxGjJ8TfDrkprCg=w1256-h799

ok, now simple question, how can I replace ID old with ID new ?
I have to notice that I did my homework but no Work, so I have to call for help :(

[hvsummer]

I uploaded the sample for my problem to google driver, take a look at it, when you try to use SQL, instead of replacing Value, it change value of source to null, very annoying problem,
when you want to change value which is used as relationship, SQL not work, it troll us...

https://drive.google.com/file/d/0B6B-2zFtnQACRzAyU3lNZk11dDQ/view?usp=sharing

[NeoPa]

I'm confused. I've already answered your question but you say it hasn't worked. Apparently this is replacing your ID value with Null.

You've also sent a picture that I can't see as permissions do not allow public viewing, and a database attachment that I haven't asked for. You may be inexperienced in such issues but copies of your work are very rarely required if the problem is explained properly in the first place. It is generally something you should only provide if it is requested of you. In this case I'm not inclined to go to that extra level of work for you. I couldn't possibly afford to do that for all th threads I help in or I would have no time for anything else.

This usually works by each of us responding to what the other posts by filling in any necessary gaps. In this case I would need to see exactly how you applied my suggestion as, going by what information you've posted, that should do exactly what you want it to. My belief, albeit working remotely so relying on information I can't check properly, is that something in the information you've provided isn't accurate.

So, what is happening? Please be as precise as you can be. If you need to include a picture then it can be done by uploading it as an attachment. I can make sure it displays correctly in the thread as long as it's uploaded properly.

[hvsummer]

[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=84 29&d=144046879 2[/IMGNOTHUMB]
here's your picture, actually, mine, but whatever, When I use SQL (query) to update/change the old value to new value, it Has not worked.. everytime instead changing old ID among mix (old and new) ID to the new one in the client data table, something's strange here, it clear those new one in the table 2...
I can't understand what is the problem here.

for example, look at the picture, if I use update query with iif to select the old one on expression, it error. if i specify it in criterial - Like "2*" - it clear the data on source table 2.

my point is, we can't do the update to the relationship itself, so I ask how to replace value with VBA code, like some Vlookup code for access or whatever can help this.

[NeoPa]

HVSummer:
my point is, we can't do the update to the relationship itself, so I ask how to replace value with VBA code

That may be your opinion. As can be seen from my earlier post, I believe there are steps to go before we jump to that conclusion and I've again requested meaningful feedback from you in order to give me something to work with. The picture's good, but only confirms a part that I already understood. I still need some clear feedback as to what's going on when it runs in order to proceed.

Doing this in VBA is possible, but if it doesn't work in SQL then there's a problem somewhere that isn't going to go away just because we try to ignore it. I can't imagine us making any progress on that front if we can't even get the communication sorted effectively for handling the simpler SQL problem.

[hvsummer]

lol, here is your result, work well with sample,
Result very good:
[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=84 35&stc=1&d=1440 478505[/IMGNOTHUMB]

lets me test on big-real data
what the hell, you save my life Neopa, thank you
but I have some confuse, did I misunderstand Access ?
in update-query design, that field "update to" I tho that it will using that name as target to update to ?
if we had this result, that should name like "update from" instead "update to" ? how funny microsoft..
since I'm bad at SQL, so I alway heavily depend on query design, and this time Microsoft hit me hard with some confuse phrase...
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8431d1440477673/b2.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8432d1440477673/b3.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8433d1440477673/b5.png[/IMGNOTHUMB]
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8434d1440477673/a1.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]http://bytes.com/attachments/attachment/8435d1440478535/a1.png[/IMGNOTHUMB]