I'm used to update queries based on criteria in other variables. In this case, I'm trying to write an update query based on criteria in other records.

I have survey responses stored like this:



If a subject has a value in Response for QstnID 10, 11, or 12 (e.g., SubjectID 1), I'd like to insert a "Yes" in his Response for QstnID = 5. Otherwise, if the subject is missing a Response for QstnID 10, 11, and 12 (e.g., SubjectID 2), insert a "No."

Any suggestions? (There's actually about 10 questions I need to check for Nulls, but they're ID's are all consecutive and adjacent.)

try the following Update query, replacing MyTableName with the name of your table, as


that should give you the "No" responses. for the "Yes" responses, change it to


hth
tina

Use two queries. Make sure you have a backup before testing.

Update table set response = "No" where qstnid = 5

Update table as A inner join
(Select subjectID from table where qstnid in [10, 11,12] and response is not null) as q1
on a.subjectid = q1.subjectid
set A.response = "Yes" where A.qstnid = 5

I tried Tina's suggestion but I have almost 1 million records to cycle through and the subqueries always fail with this many.

So I'd like to try your approach, but I get this error:

"Can't represent the join expression a.SubjectID = q1.SubjectID ..."

Ah ... I changed the [ ] to ( ) and it ran fine. As far as I can tell, it worked. Thanks!