sql

[subs]

wgt wgt range
100 1-500
2000 1000-5000
60000 30000-60000
250 1-500


i have a column called as wgt in one of my tables. There are thousands
of records under this column. Also i have an column called as wgt
range . This colmn is empty. Now i want to update this column with
data as seen above. i need a update query with a criteria which can
check the wgt in the wgt column and then update the wgt range column
accordingly. for example if the wgt is 100 then wgt range is 1-500
becuse 100 is between 1 and 500 and so on.

I need a query which can update all the rows at once . i tried in
design grid but it is not updatabale at once. Is there a SQL query.

Thanks for yr help

 

[Lord Kelvan]

what are all the ranges

ie

1-500
501-1000
1001-5000
5001-10000
10001-30000
30001-60000

do you actually want to use the wgt range colum for anything other
than displaying it because if you do you sould have minwgtrange and
maxwgtrange columns rathar than just wgtrange

please tell the range and your table name so i can make you a sql
query.

Regards
Kelvan

 

[fredg]

wgt wgt range
100 1-500
2000 1000-5000
60000 30000-60000
250 1-500

i have a column called as wgt in one of my tables. There are thousands
of records under this column. Also i have an column called as wgt
range . This colmn is empty. Now i want to update this column with
data as seen above. i need a update query with a criteria which can
check the wgt in the wgt column and then update the wgt range column
accordingly. for example if the wgt is 100 then wgt range is 1-500
becuse 100 is between 1 and 500 and so on.

I need a query which can update all the rows at once . i tried in
design grid but it is not updatabale at once. Is there a SQL query.

Thanks for yr help

Why?
Wgt Range is a calculated field. It's value determined by the value
stored in the wgt field.
There is no need to have this calculated field even in your table.

Any time you need the range simply compute it. You can create a user
defined function, pass it the wgt value and the function will return
the correct range.

Function GetRange(wgtIn as Long) as string
Select Case wgtIn
Case 1 to 500
GetRange = "1 - 500"
Case 501 to 1000
GetRange = "501 - 1000"
Case 1001 to 5000
GetRange = "1001 to 5000"
Etc.
End Select
End Function

Then just call the function when and if you need it:
=GetRange([wgt])