Use of a calculated fied in an update query

L

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

MGFoster

Luther said:
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-----
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top