calculate field with if

[Guest]

I can't find out how to do this in an Access table! The table has 2 fields,
one "depth" holds data records, the other "class" is initially blank. The
values for "Class" are to be calculated in this way: If depth < 200, value
of Class is the text "Shallow", otherwise "Not." (Maybe I type some formula
in the table's Class field properties?)

 

[Guest]

You can not do it in a table but in an update query. Best is to just
calculate it when needed as old data calculation may be out of date.

 

[Guest]

Hi

This isn't possible at the table level.

Further, it is generally considered bad practice to store "derivable" data
alongside the data it can be derived from.

In this case you should only store the depth in the table.

When it comes to displaying the data to the user you will build queries and
forms within which you can calculate the class.

hth

Andy Hull

 

[Guest]

Thanks Karl and Andy (next post). Update query did the trick. I actually
had to get into nested if statements, and the SQL is given below.
I think I understand the advice about not updating one field from data that
might change later. However, in this case, the data are not likely to
change, as they give water depths at various locations in the ocean, which
change only very slowly (except at locations with heavy storm or current
forces).

UPDATE [Joined Prot Area Block]
SET [CLASS] =
IIf
([BLK_MAX_WTR_DPTH]<200,"m 0 to 200",
IIf
( [BLK_MAX_WTR_DPTH]<400,"m 200 to 400",
IIF
([BLK_MAX_WTR_DPTH]<800,"m 400 to 800",
IIF([BLK_MAX_WTR_DPTH]<1600,"m 800 to 1600", "m 1600 plus"
)
)
)
);