sql query needed urgent

[lee]

item len width height c/nc
shoes 10 5 7
racket 4 23 12
rabit 5 12 14


i have a table with the fields item, length, width and height. i will
have to add another field c/nc. This field is calculated by using the
formula - ((height dimension*10)/(lowest dimension))- third dimension

if the value of this formula is less than 10, then the field c/nc will
be c . if the value is greater than 10, then it will be nc.

what will be the sql query which will calcualte the formula and based
on the formula , populate the c/nc field. thanks

 

[Bob Barrows]

item len width height c/nc
shoes 10 5 7
racket 4 23 12
rabit 5 12 14


i have a table with the fields item, length, width and height. i will
have to add another field c/nc. This field is calculated by using the
formula - ((height dimension*10)/(lowest dimension))- third dimension

if the value of this formula is less than 10, then the field c/nc
will be c . if the value is greater than 10, then it will be nc.

what will be the sql query which will calcualte the formula and based
on the formula , populate the c/nc field. thanks

[c/nc]: IIf(10*height / IIf(width>len, len, width) -
IIf(width>len, width, len)) >10, "nc", "c")

If only it was that simple
I interpret "lowest dimension" to mean "lowest of all three dimensions", not
"lower of width or len".
I'm working on a query that accomodates this requirement. Of course, this is
complicated by the need to define what the "third demension" will be if
height is the lowest.

 

[Bob Barrows]

item len width height c/nc

These are bad names for your fields. All are reserved keywords and might
cause you future problems that will be very hard to debug. Making the names
more descriptive (ItemName, Itemlen, Itemwidth, ItemHeight, etc.) will serve
to both eliminate the reserved keyword issue and self-document your database

shoes 10 5 7
racket 4 23 12
rabit 5 12 14


i have a table with the fields item, length, width and height. i will
have to add another field c/nc.

Hopefully, you are not really intending to add this field to the table.
Calculations like this should be done in queries or reports. The results
should not be stored unless there is some need to store historical values,
eg, you need to know what the value was last year as opposed to this year.

This field is calculated by using the
formula - ((height dimension*10)/(lowest dimension))- third dimension

I have a couple questions before I can start to work on a solution:

Are any of these dimension fields allowed to contain zero or Null?
Is height allowed to be the lowest dimension? If so, how is "third
dimension" defined? If not, Marshall's solution is a good starting point.

 

[Bob Barrows]

item len width height c/nc
shoes 10 5 7
racket 4 23 12
rabit 5 12 14


i have a table with the fields item, length, width and height. i will
have to add another field c/nc. This field is calculated by using the
formula - ((height dimension*10)/(lowest dimension))- third dimension ^

if the value of this formula is less than 10, then the field c/nc will
be c . if the value is greater than 10, then it will be nc.

what will be the sql query which will calcualte the formula and based
on the formula , populate the c/nc field. thanks

If Marshall's interpretation that you meant the lower of len and width is
correct, I believe the initial hyphen was actually part of the formula,
which means that his suggested expression should be changed to this:

[c/nc]: IIf(
IIf(width>len, width, len)) - 10*height / IIf(width>len, len, width)>10
, "nc", "c")

 

[lee]

item  len  width height     c/nc
shoes 10   5       7
racket 4    23      12
rabit   5     12     14

i have a table with the fields item, length, width and height. i will
have to add another field c/nc. This field is calculated by using the
formula - ((height dimension*10)/(lowest dimension))- third dimension

                 ^

if the value of this formula is less than 10, then the field c/nc will
be c . if the value is greater than 10, then it will be nc.

what will be the sql query which will calcualte the formula and based
on the formula , populate the c/nc field. thanks

If Marshall's interpretation that you meant the lower of len and width is
correct, I believe the initial hyphen was actually part of the formula,
which means that his suggested expression should be changed to this:

[c/nc]: IIf(
IIf(width>len, width, len))  - 10*height / IIf(width>len, len, width)>10
, "nc", "c")

Thanks Bob and Marshall, I will give it a try