Update Query Question

Hello. I have a table where I want to change the data in a specific field based on what that field contains using another table as a cross reference.

I could use a search and replace function: replace x with y but I have about 29 different values in that column I need to change to new values.

I have the cross reference table already but not sure of how to proceed or the sql syntax

update tablename, set fieldname = y, where fieldname = x..... or something like that.

Any assistance is appreciated....

Ed,
You are breaking the laws of normalization which will only get your into trouble. You should only store any given piece of data in one place. Clearly you are storing the same data in more than one table. The efficient way to do this is to have a query pull the right data for display. Anyway, I'll answer your question. Create a query with a link between the two tables on a common field which is hoping the primary key in one of the tables. Switch the query to an update query. In the update row, put the name of the field which you want to update to.
Big Man T

Hi, thanks for the reply. I may not have clearly explained what I'm trying to do.

In the data table is a field where I want to change the data from one thing to another:

AA becomes 112
BB becomes 175
CC becomes 210

This is an illustration only, not actual data. the text represents a code and the numbers are simply a replacement for the code. There are multiple records with the same code, I could simply open the table, highlight the field and do a search and replace but as I have quite a number of replacements I am trying to do it through an update query..... It is a one time task.

Edward.

"eDWARD" wrote

In the data table is a field where I want to change the data from one thing to
another:

AA becomes 112
BB becomes 175
CC becomes 210

This is an illustration only, not actual data. the text represents a code and the
numbers are simply a replacement for the code. There are multiple records with the
same code, I could simply open the table, highlight the field and do a search and
replace but as I have quite a number of replacements I am trying to do it through an
update query..... It is a one time task.

Hi Edward,

I think this is what you want:

UPDATE
tbl1 INNER JOIN tbl2
ON tbl1.PK = tbl2.PK
SET tbl1.f1 = tbl2.f1

In query designer

-add both tables
-join key(s) of both tables by drag and drop
a join field from original table to join field
of lookup table
-drag and drop field from original table you want
to update down to a field row
-change query from select to update
-in UpdateTo row, type in
tablename.fieldname from your lookup table.

test on copy of db to verify result

Good Luck,

Gary Walter

thanks, i'll try that out.