Edit a Querry

[Guest]

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
Arnold Siemsen

 

[Gary Walter]

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

 

[John Spencer]

In the query design view (the grid):

Field: GroupNum: Switch([NumberColumn]>15, 1,
[NumberColumn]>0,2,[NumberColumn]<=0,3)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John and Gary. I will use John's method as it's easier. Thanks to all.
Arnold

In the query design view (the grid):

Field: GroupNum: Switch([NumberColumn]>15, 1,
[NumberColumn]>0,2,[NumberColumn]<=0,3)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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
Arnold Siemsen

 

[John Spencer]

Just understand that easier is NOT necessarily better. The better solution
would involve a table, so that if (when) the requirements change you only
need to change the records in the table. Using the Switch function, you
would have to locate and rewrite the function everywhere it is being used.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John and Gary. I will use John's method as it's easier. Thanks to
all.
Arnold

In the query design view (the grid):

Field: GroupNum: Switch([NumberColumn]>15, 1,
[NumberColumn]>0,2,[NumberColumn]<=0,3)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message

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
Arnold Siemsen