"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.
0
Utf
3/8/2008 9:14:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
522 Views

Similar Articles

[PageSpeed] 6

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" <Shaukat@discussions.microsoft.com> wrote in message
news:6EEA2C12-31A2-4EEA-BA56-7CB0DFA84E7E@microsoft.com...
>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. 

0
Allen
3/8/2008 10:09:35 AM
On Sat, 8 Mar 2008 01:14:01 -0800, Shaukat <Shaukat@discussions.microsoft.com>
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]
0
John
3/8/2008 6:03:21 PM
Reply:

Similar Artilces:

"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 ...