"Use of OR condition in UPDATE Query"

I have a table that lists a set of diseases in several columns (say 2
columns, Disease1, Disease2). I want to create a new column (let us say,
Diabetes) which would be coded as "Yes" for a row for which any one of the 2
columns has an entry for "Diabetes". Otherwise, it would be coded "No". I
t would be an update query. I tried the following sql statement but it does
not work:

Update Table1 Set Table1.Diabetes = (IIF(InStr(1,([column1]), "Diabetes")0
Or (IIF(InStr(1,([columnn2]), "Diabetes")0, "Yes","No"));
I have tried various versions of this code but nothing seems to work.
Any ideas? or Is it that UPDATE query does not allow for multiple conditions
(Or and AND conditions)?
Thanks in anticipation for the help.

This kind of thing might work:
UPDATE Table1 SET Diabetes = TRUE
WHERE Column1 Like "*Diabetes*"
OR Column2 Like "*Diabetes*";

But could I *plead* with you not to design a table in this way?
If one record (patient?) could have several diseases, you really need to
create a related table where a patient can have many *records* identifiying
the diseases that apply.

For an explanation, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Shaukat" wrote in message
...
I have a table that lists a set of diseases in several columns (say 2
columns, Disease1, Disease2). I want to create a new column (let us say,
Diabetes) which would be coded as "Yes" for a row for which any one of the
2
columns has an entry for "Diabetes". Otherwise, it would be coded "No". I
t would be an update query. I tried the following sql statement but it
does
not work:

Update Table1 Set Table1.Diabetes = (IIF(InStr(1,([column1]),
"Diabetes")0
Or (IIF(InStr(1,([columnn2]), "Diabetes")0, "Yes","No"));
I have tried various versions of this code but nothing seems to work.
Any ideas? or Is it that UPDATE query does not allow for multiple
conditions
(Or and AND conditions)?
Thanks in anticipation for the help.

On Sat, 8 Mar 2008 01:14:01 -0800, Shaukat
wrote:

I have a table that lists a set of diseases in several columns (say 2
columns, Disease1, Disease2).

Then you have an incorrectly designed table. This is reasonable design for a
spreadsheet; it's *WRONG* for a relational table.

I want to create a new column (let us say,
Diabetes) which would be coded as "Yes" for a row for which any one of the 2
columns has an entry for "Diabetes". Otherwise, it would be coded "No". I
t would be an update query. I tried the following sql statement but it does
not work:

And now you want to dig your bad design hole even deeper!! This would not only
violate normal form, it would also store the same information redundantly in
two fields.

STOP. If you're going to use Access, you'll find it much better to use it
correctly!!! Consider instead using THREE tables to model the many to many
relationship:

Patients
PatientID
LastName
FirstName
other bio information but *no* disease information

Diseases
DiseaseID perhaps the standard medical insurance code
DiseaseName

PatientConditions
PatientID link to Patients
DiseaseID link to Diseases
any information about this disease in this patient, e.g. date of onset,
severity, comments

--

John W. Vinson [MVP]