Adding to a field using a query

[Thorson]

I already have a table "tblEarTag" set up with ~3000 values in a field
(EarTag) I would like to change ~590 of those values to have a 08 in front
of what is currently there. They 590 are identified by their presence in
another table "tblCustomFeedAnimals". Every EarTag in "tblCustomFeedAnimals"
needs to be updated in the main table "tblEarTag", but only those values.

This is a one time change it will not happen in the future. Is there a way
to do this? My best guess was to use a query and do an "Iif-Then-Else"
Something like: if it is present in "tblCustomFeedAnimals" then add "08" to
the beginning of the field, if not then do nothing.

Any suggestions?

 

[Beetle]

First, MAKE A BACKUP and run this on the backup copy to make sure
it gives you the results you want.

Use an Update query like;

UPDATE tblEarTag SET tblEarTag.EarTag = "08" & [tblEarTag]. [EarTag]
WHERE tblEarTag.EarTag IN (SELECT tblCustomFeedAnimals.EarTag
FROM tblCustomFeedAnimals)

 

[John W. Vinson]

I already have a table "tblEarTag" set up with ~3000 values in a field
(EarTag) I would like to change ~590 of those values to have a 08 in front
of what is currently there. They 590 are identified by their presence in
another table "tblCustomFeedAnimals". Every EarTag in "tblCustomFeedAnimals"
needs to be updated in the main table "tblEarTag", but only those values.

This is a one time change it will not happen in the future. Is there a way
to do this? My best guess was to use a query and do an "Iif-Then-Else"
Something like: if it is present in "tblCustomFeedAnimals" then add "08" to
the beginning of the field, if not then do nothing.

Any suggestions?

If there's a relationship defined between the tables, you can do the update
with a Join:

UPDATE tblEarTag.EarTag
INNER JOIN tblCustomFeedAnimals
ON tblEarTag.EarTag = tblCustomFeedAnimals.EarTag
SET tblEarTag.EarTag = "08" & tblEarTag.[EarTag]

This assumes that EarTag is the Primary Key of tblEarTag and is the field that
you'ld use to match the records in the two tables.