Need Query Assistance

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Is it possible to write a query to solve the issue below?
If so, "HOW"? TIA

I have a Database with several fields, at the moment, I'm
only concerned about 4 of them. I need a query (or a
solution) to the following problem.

If field 1 contains data, copy it to field 4.
If field 1 does not contain data, then field 2 MINUS field
3 and put the answer into field 4 (all are number fields)

Please help
Thank you.
 
Ken

Some confusion of terms, there. Databases include tables, which have
fields. I'll assume you have a table with 4 fields you're concerned with.

A review of the tabledbdesign newsgroup will reveal a strong bias against
storing calculated data in a relational database (i.e., Access). Note that
this is generally not a good idea, as it adds to your overhead for keeping
fields synchronized.

Would it be sufficient to "know" the value you are trying to calculate and
store? If so, use a query to calculate that value, on-the-fly. You can use
an IIF() statement in a query to determine the value, something like:

CalcdValue: IIF(IsNull([Field1]),[Field2] - [Field3],[Field1])

There are SO many ways this could go wrong!

You didn't mention if [Field1] was numeric, or if it might hold a space, or
a zero-length string (if so, the IsNull() statement won't catch it).

If either Field2 or Field3 is null, subtracting them will be null -- how do
you want to handle that?

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top