COMBO BOX SQL PROBLEM WITH CALCULATED FIELD

[fsweb2002]

Hi

I have a combo box. One of the columns is calculated from a value on another column.

Example

ALBANY NEW YORK 123 110.7
CHICAGO NEW YORK 450 405

My code is..

SELECT ORIGIN,PORT,KMS,KMS*0.9 AS RATE

All works well, however the formula KMS*0.9 needs to be "dynamic".

I actually have a scale of
0-100 kms = 0.10 per km
101-300 kms=0.095 per km
>300 kms = 0.90 per km

How can I test the value on the 4th (calculated) column so it can check the rate as per scale above and use the correct rate ????

I hope I explained myself correctly...

Thank you so much in advance

 

[IFELSETHEN]

Hi, well if you were using Oracle it's called DECODE, if you were using SQL2000 it's called CASE, however you are using Access (so sorry). You could use sub queries as the calculated field(s), but you would have four extra fields. Three would be blank and one would have a value.

John

 

[raskew]

The Switch function allows us to create a
SELECT CASE…END SELECT scenario from
within a query.

To demonstrate this, here's a table fsweb2002
using the data in the first three columns of your
example.

 [table]
Origin	Port	KMS
Albany	New York	123
Chicago	New York	450
[/table]

Here's query-SQL (copy/paste into a new query) that
will respond to the KMS field, according to your example:

SELECT fsweb2002.Origin, fsweb2002.Port, fsweb2002.KMS, [KMS]*Switch([KMS]<101,0.1,[KMS]<301,0.095,True,0.9) AS Rate
FROM fsweb2002;

Only problem being, your example of Albany/New York = 110.7
doesn't agree with the example of 101-300 kms=0.095 per km. In
fact, the multiplier that makes 123 * x = 110.7 is 0.9. Resolve that
glitch and it should work for you.

Lookup: Switch function

 

[fsweb2002]

thanks raskew, SWITCH works fine! (I didn´t even know it existed...)

IFELSETHEN, as you can see, there are almost always a way to do something in Access too, no need for such powerful systems you mentioned.
Remember than Access is a program just about everyone has in their PC, but Oracle/SQL2000 is much harder for people to have in their PC´s...

Obviously Access is not "the tool" however it hasn´t let me down yet, everything I wanted to do I managed to do it...sometimes it got complicated but I managed it to work.

Thanks guys