Update Query question

[formcreator]

Hello,
This seems to be a very simple update query but for some reason I am not
able to get my arms around it. I have 2 fields with data as shown below and I
want to update the third field with the value CC if any of records have CC in
field 2 when grouped by field1.
field1 field2 field3
1 CC CC
1 HS CC
2 HS HS
2 HS HS

When field1 is 1 and even though field2 is HS I want to update field 3 as CC
because one of the records has field1 =1 and field2 = CC

Any suggestions is greatly appreciated.

Thanks
Shri

 

[Jack Leach]

Eh? You want to update when:
a) field 2 is CC
or..
b) if field1=1 and field2=HS?

I think your WHERE clause would look like this:

"WHERE (field2 = ""CC"") OR (fld1=1 AND fld2 = ""HS"")"

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John Spencer]

UPDATE YourTable
SET Field3 ="CC"
WHERE Exists (SELECT * FROM YourTable WHERE Field1 = 1 and Field2="CC")

That updates EVERY RECORD IN THE TABLE if any record in the table has Field1 =
1 and field2 with a value of "CC". If that is not what you want to do, then
you need to further explain what you are attempting to do or modify the
subquery to limit whether or not you have a match.

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