update item code

[Newbie]

Hello,
We like to replace some of our item code.
i.e. sales history database, item database...

We have a a replacemene code database has 2 fields.
old item code and new item code.

How can we write a queryto replace our item database with new item code?

i.e.
item database has 3 fields (about 10,000 items)
item code, description, quantity
replacemene code database has 2 fields (about 500 items need replace new code)
old item code and new item code

thansk

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

UPDATE [ItemsTable] INNER JOIN [ReplacementsTable]
ON [ItemsTable].[Item Code] = [ReplacementsTable].[Old Item Code]
SET [ItemsTable].[Item Code] = [ReplacementsTable].[New Item Code]

To build that In the design view of a query
-- Add both tables
-- Drag from Item Code to Old Item Code to setup the join
-- Add Item Code to the list of fields
-- SELECT Query: Update from the menu
-- In the UPDATE TO under Item Code enter
[Name of Replacement Table].[New Item Code]
-- Be sure you have included the square brackets or the replacement value
will be turned into text that reads exactly what you have typed.
-- Select Query: Run from the menu

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..