Find and Change

[Guest]

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.

 

[Carl Rapson]

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.

UPDATE

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

WHERE Type="CCC")

Carl Rapson

 

[Guest]

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";