My Reply:
1. Please can you clarify why you need to do this when you can just look at the detail in the second table.
Answer 1) I have a huge database with a field for City Names, and I want to get their accurate & standard Names from a Reference table.
I still do not understand what you are trying to achieve. Please provide a full example (your current example is not detailed enough) - put into an excel spreadsheet if it helps
Answer - The attached database is a demo file with the same kind of layout for what I see in the actual database.
2. some data that the user would want to update, show relevant data from both tables, before update and after update
Answer 2) 2nd table is just a reference table for getting the accurate & standard names of cities, and their correct State Names
for example: "Bombay;" should be updated as "Mumbai" in the 1st table.
3. some data the user would not want to update, show relevant data from both tables and explain why the user would not update
Answer 3) In case, in our 1st database we get any city name which is not listed in our 2nd table (reference table)'s "Raw_City" field, that means we need to update that (make a new entry) in the 2nd table for further use, so that next time that will be searchable in there.
4. having updated the table, an example of what you use the data for
Answer 4) After getting the standard City names and State names in the 1st table, I will need to perform some analysis using Pivottable tricks.
5. If you use Mumbai as an example - how would you differentiate between the second and third records in the 2nd table?
Answer 5) Each full name string is connected by a ";" sign in the tbl2_RefrDB.[Raw_City] field.
So if you want to look for Mumbai, then search for "Mumbai;" string in tbl2_RefrDB.[Raw_City] field, if found then get the tbl2_RefrDB.[Standard_State] and tbl2_RefrDB.[Standard_City] fields into 1st table to have a most accurate database.
You can think above# 5 in another way as below too:
tbl2_RefrDB.[Raw_City] field contains most possible city name strings in itself as below, but afterall the standard city name is "Mumbai" only:
Mumbai & Mumbai(Suburban);
Mumbai;
Greater Mumbai;
Bombay;
What you require is potentially very complex and before spending a lot of my time I want to be sure it is necessary to your process. As explained before, you need to investigate normalisation - I cannot see how your table2 raw_city field is going to do what you want
Answer - let's think these two tables in excel VBA.
If we want to update the Standard Names from sheet2 (table2) into sheet1 (table1), then we need to write a program using Array & Split functions:
Code:
' below VBA code is just for understanding the validation part in excel as we have multiple strings in single cell:
Dim x as Long, y as Long, z as long
Dim var as variant
For x = 1 to Sheet1.Range("A" & rows.count).end(xlup).row
For y = 1 to Sheet2.Range("A" & rows.count).end(xlup).row
var = Split(Sheet2.Range("A" & y).value,";")
For z = Lbound(var) to Ubound(var)
if Sheet1.Range("A" & x).value = var(i) then
'--- City name found, so pick the standard City and State names from Sheet2, then update their details on Sheet1
'--- early exit from loop
GOTO iNxt:
End if
Next z
var = Empty
Next y
iNxt:
Next x
then run a loop...
...and here in MS Access i want to avoid looping on large datasets. 
Identify strings into 2nd table's records then update 1st table (kind of Vlookup)
Windows 7 64bit 
Access 2013
Reply With Quote
Originally Posted by SunOffice
