Arnold Siemsen said:
I have a column of numbers ranging from -15 to +95 in a query. In the next
column over I want to convert all numbers in the first column above 15 to
1,
all numbers above 0 but below 15 to 2 and all numbers below 0 to 3.
Please
help. Thanks
This might seem like "extra work" to you,
but I suggest making a translation table.
That documents the logic, plus can be
easily changed if the logic changes.
tblTranslate
GTEQ LT ConvertTo
-15 0 3
0 1 ?
1 15 2
15 16 ?
16 96 1
notice how it also helps with logic as well...
Did you mean for second column to get
converted for first column of 1 and 15?
If not, then delete those rows from tblTranslate.
Anyway...your update query should be easy...
UPDATE
yurtable As t1
INNER JOIN
tblTranslation As t2
ON
t1.Column1 >= t2.GTEQ
AND
t1.Column1 < t2.LT
SET
t1.Column2 = t2.ConvertTo;
Above is one "quick-and-dirty" method....
I imagine there will be response showing
how inefficient or mathematically challenged
this method is....
Hopefully, though, you understand the process.
Get the logic right in the table and the update
query is child's play...
