Update Query

[Luther]

I have a database with the following fields:

- enrollment (number)
- staff (yes/no)
- IPA (yes/no)
- Network (yes/no)
- Group (yes/no)

I have created another field (RESULT), which I would like
to populate with a code, based on what's in the above. I
need to build criterias for the here are some examples:

1. enrollment <=99,999 and JUST staff=yes (all others=no,
then RESULT = 20
2. enrollment <=99,999 and a mixture of yes in the staff,
IPA, network or group fields, then result=50. To clarify,
any combination of yes in these fields and enroll <=99999
then result = 50


If I can get these setup, I can duplicate the code for all
the other variations.

Appreciate this in advance !!! I probably need some hand
holding, since this is my first attempt at this somewhat
complicated query.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your [Result] column is a calculated column. It is usually not a good
idea to store the value of a calculated column because each time one of
the criteria changes the value in the calculated column has to be
updated.

SQL Server 2000 does have calculated columns, but they do not store
values in the table. Every time a record is displayed the calculated
column expression (the calculation formula) "runs" and displays the
calculated value. This value is not directly updateable. To update it
the expression's criteria has to be changed. Therefore, the calculated
column is dynamic and does not require UPDATEs to maintain its value.

That said . . .

In direct answer to your question: The easiest way is to make 2 UPDATE
statements:

UPDATE TableName
SET result = 20
WHERE enrollment <= 99999
AND staff = True
AND IPA = False
AND network = False
AND [group] = False

UPDATE TableName
SET result = 50
WHERE enrollment <= 99999
AND (staff = True
OR IPA = True
OR network = True
OR [group] = True)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIb0VoechKqOuFEgEQL7aACgh489Nys1+7UEuh/zHKrjQJTn698An0n4
rt/Z1opppPzODjNBL49f7D4w
=Pvd/
-----END PGP SIGNATURE-----

 

[Guest]

Thank You !!!! This has worked perfectly !!!!

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your [Result] column is a calculated column. It is usually not a good
idea to store the value of a calculated column because each time one of
the criteria changes the value in the calculated column has to be
updated.

SQL Server 2000 does have calculated columns, but they do not store
values in the table. Every time a record is displayed the calculated
column expression (the calculation formula) "runs" and displays the
calculated value. This value is not directly updateable. To update it
the expression's criteria has to be changed. Therefore, the calculated
column is dynamic and does not require UPDATEs to maintain its value.

That said . . .

In direct answer to your question: The easiest way is to make 2 UPDATE
statements:

UPDATE TableName
SET result = 20
WHERE enrollment <= 99999
AND staff = True
AND IPA = False
AND network = False
AND [group] = False

UPDATE TableName
SET result = 50
WHERE enrollment <= 99999
AND (staff = True
OR IPA = True
OR network = True
OR [group] = True)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIb0VoechKqOuFEgEQL7aACgh489Nys1+7UEuh/zHKrjQJTn69 8An0n4
rt/Z1opppPzODjNBL49f7D4w
=Pvd/
-----END PGP SIGNATURE-----

I have a database with the following fields:

- enrollment (number)
- staff (yes/no)
- IPA (yes/no)
- Network (yes/no)
- Group (yes/no)

I have created another field (RESULT), which I would like
to populate with a code, based on what's in the above. I
need to build criterias for the here are some examples:

1. enrollment <=99,999 and JUST staff=yes (all others=no,
then RESULT = 20
2. enrollment <=99,999 and a mixture of yes in the staff,
IPA, network or group fields, then result=50. To clarify,
any combination of yes in these fields and enroll <=99999
then result = 50

.