Use of a calculated fied in an update query

[Luther]

Hi,

Is it possible to use a calculated fied in an update query? For example,
this scenario:

1. create a calculated field that counts something (plancount:
field1+field2+field3)
2. and (in the same query), use the new field in further updates

In the 2nd step, I would like to say something like this:

if plancount < 1, then update [SomeOtherField] = value

Any help would be appreciated.

Thanks.

 

[MGFoster]

Hi,

Is it possible to use a calculated fied in an update query? For example,
this scenario:

1. create a calculated field that counts something (plancount:
field1+field2+field3)
2. and (in the same query), use the new field in further updates

In the 2nd step, I would like to say something like this:

if plancount < 1, then update [SomeOtherField] = value

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

Yeah, like this:

UPDATE table_name
SET column_name = SomeValue
WHERE Nz(colA)+Nz(colB)+Nz(colC) < 1

Use Nz() 'cuz sometimes the column value may be NULL and the Nz()
function changes NULL to zero.

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

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

iQA/AwUBRFkiR4echKqOuFEgEQLKFwCfc5qNYYSMkftHwX/vpYsN/yXVuV4An0Us
WYv+UpdelMsEyC4sBIfI8A+3
=vWGs
-----END PGP SIGNATURE-----

 

[Tom Ellison]

Dear Luther:

When you have a calculated value, especially a very simple one, it is far,
far better NOT to store it in a table. Simply use a query, a form, or a
report to add it up LIVE whenever it is retrieved. Otherwise, you're
saddled with making sure that, no matter how any of the components of this
value are changed, the value is also changed. Since this requirement
includes what happens if someone just opens the datasheet of the table and
makes a change, and since you cannot program for that, you have a
considerable headache.

When you calculate a new column in a query, you cannot use that column
within the same query. You can always, however, use the same calculation
within that query wherever you need that value. This is fairly easy to do,
and solves all your problems.

Tom Ellison