Update Query question

F

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
 
J

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)
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Customized display order for fields 1
Update Set Values 3
help 5
do loop through a table 2
Shift several columns in table 4
Listbox Sideways 12
Format portion of text sting in upper case 1
access form 1

Top