Replace names in a field

[KevinS]

Using Access 97 for a case management system we sometimes have to move a lot
of cases from one case worker to another case worker. We only want to move /
replace the caseworkers name for cases that are still open.

If I do a query on the caseworker name and where case status is open I will
get the cases I need. Now I can copy and paste to each record but that is
time consuming. What I would like to do is having the query replace one name
with another.

Is this possible?

 

[Jeff Boyce]

"How" depends on "what" ... Please describe the data structure in your table
so we can help suggest a way to do this with a query.

(hint: I can't be sure from your description, but it sounds like you'll be
using an Update query)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Try using an UPDATE query.

UPDATE MyTable
SET CaseWorker = "New Name"
WHERE CaseWorker = "Old Name"

 

[KevinS]

What kind of information do you need from to answer your question?
KevinS

 

[John Spencer MVP]

If you already have a query that identifies which records you want to change
you probably can turn that into an update query.

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.

In Design view, select Query: Update.

In the UpDate To under the Caseworker enter the name of the new caseworker and
then select Query: Run from the menu (or click the run button - a red
exclamation mark).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Data structure in your table -- field names, data types.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KevinS]

This looks like it should work and I believe it is what I am looking for. I
am still having a problem with the records not updating.

I have the following fields in a query:
caseworker number 0134
CW first name "Dave"
CW lastname "Thomas"
caseopen = True

Update query
caseworker number 2345
CW first name "John"
CW last name "Smith"
caseopen = True

When I run the query I get a message that I am about to update 20 records.
Do I want to proceed? I say OK and Dave Thomas is still there.

Whatever I am doing wrong has to be minor.
Thank you,
KevinS

 

[John Spencer MVP]

Can you post the SQL view of your update query? I can't tell from what you
have posted what is happening.

Did you want to update the first record to match the second record?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KevinS]

John,
Thank you for responding. I figured out what I did wrong.
KevinS

 

[Jeff Boyce]

Kevin

Other folks may run into the same issue you faced. If you'll post your
solution, you'll be helping them.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KevinS]

When you pick 'Update query' from the query drop down menu; one of the
options is update. You will notice that the sort/criteria rows nows has
'Update to' down there.

Pick the field you want and pick the old value in one row and the new value
in the other. Click Run! You will get a message telling you that you are
about to update xx records; Do you want to proceed? When you say yes the
changes to the database are done.

 

[Jeff Boyce]

Thanks, Kevin.

Jeff

When you pick 'Update query' from the query drop down menu; one of the
options is update. You will notice that the sort/criteria rows nows has
'Update to' down there.

Pick the field you want and pick the old value in one row and the new
value
in the other. Click Run! You will get a message telling you that you are
about to update xx records; Do you want to proceed? When you say yes the
changes to the database are done.