database question

[mp3er3000]

i have two tables, table A contains old customer names, and table B contains new customer names. Each table has about 1000 rows. I want to write a query or vba macro to first look up in the table B, if a name is existed in old customer(table A) table then replace the customer name field to "existed" to table B, but not deleting the whole record. Basically, keep that person records on there except their name. One way i could think of is recursively searching but it would take a very long time. Can anyone suggest a better method? thanks

 

[sdk]

Hi

Is there a table with the names mapped to each other? you need a common key between the two, then run an update query to overwrite the exstg name with the new one.

Link the common key from the two tables in a query, change the query type to an update (menubar: Query >> Update Query) and run. Only matches between the tables will be run.

Cheers

S

(and politely, your title is a little vague - try using something a little more discriptive

 

[mp3er3000]

yes, Could you please show me how this could be done? Thanks

 

[sdk]

Hi

(You must have a common attribute between the two tables, eg the second table must have the old name and the new, or there is a common identifier between the two tables)

[ul]
[li]Make a backup copy of the table you are changing (more on this later)[/li]
[li]Create a new query[/li]
[li]Add both tables[/li]
[li]join the two tables by the common attribute[/li]
[li]drop the name field that you wish to update onto the (QBE) grid at the bottom of the query design view
change the query type to an update (menubar: Query >> Update Query)[/li]
[li]In the grid, there is an 'Update To' field. In here write: "[YourOtherTableName].[FieldNewName]" (without qutoation marks and obviously substituting the table and field names)[/li]
[li]Run the query[/li]
[/ul]
The process will take only a few seconds to run, and will ask you to confirm the changes and advise how many records it will change (BUT it doesn't show you what it is changing). Because these are not undo-able, and one day you will run a data manipulation query and bugger your data, you need the backup for when that happens. Once you have checked.

If this doesn't give you the expected results, post your table structures (Names, field names) and what the name is of the common fields is, and we can take it from there.

Cheers

S