Find and Change

J

James Kennedy

In basic terms: I have a table with fields of Social Security No, Name, and
Type (3 letters). There are multiple occurrences of the same SSN and Name,
but there may be different Types (e.g., AAA, BBB, CCC, etc). I need to find
all occurrences of Type CCC. I don't need to find anyone with Types AAA or
BBB. However, if I find multiple occurrences of the same SSN and Name, but
one record has AAA and another has CCC, then I need to change the AAA to CCC.
In other words, if a person has the Type CCC, then all occurrences of this
person need to have CCC. I hope this makes sense.

Any suggestions? Thanks for your help in advance.
 
C

Carl Rapson

James Kennedy said:
In basic terms: I have a table with fields of Social Security No, Name,
and
Type (3 letters). There are multiple occurrences of the same SSN and
Name,
but there may be different Types (e.g., AAA, BBB, CCC, etc). I need to
find
all occurrences of Type CCC. I don't need to find anyone with Types AAA
or
BBB. However, if I find multiple occurrences of the same SSN and Name,
but
one record has AAA and another has CCC, then I need to change the AAA to
CCC.
In other words, if a person has the Type CCC, then all occurrences of this
person need to have CCC. I hope this makes sense.

Any suggestions? Thanks for your help in advance.
Click to expand...

UPDATE

SET [Type]="CCC"
WHERE Type<> "CCC" AND SSN IN
(SELECT SSN FROM
WHERE Type="CCC")

Carl Rapson
 
K

KARL DEWEY

Use two queries unless you know subquires. Change table name from James to
your table. The first query is named James_1.
SELECT James.Type, James.[Social Security No]
FROM James
WHERE (((James.Type)="CCC"));

UPDATE James_1 INNER JOIN James ON James_1.[Social Security No] =
James.[Social Security No] SET James.Type = "CCC";
 
Top