"Use of OR condition in UPDATE Query"

[Shaukat]

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.

 

[Allen Browne]

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

 

[John W. Vinson]

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>