Compare a value to two values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a field "Price". and another table with Price range
"Price Low" and "Price High".
For example:
Table 1
Price Adj. Price
12
23
34

Table 2
Price Low Price High Relativity Low Relativity High
0 10 1 1.1
10 20 1.1 1.2
20 30 1.2 1.3
30 40 1.3 1.4
I do not want to use If then else clause to compare the price to range.
Because whenever I change table2 I will have to manual change the values in
if then clause.

I am wondering if there is a way to look up in table 2, if price is in one
of the range then I can assigh a value: Adj. Price to the corresponding price
in table 1: E.g. (Price High-Price)*Relativity Low +(Price-Price
Low)*Relativity High.

For example, 23 is between 20 and 30, then Adjusted price of 23 is
(30-23)*1.2+(23-20)*1.3.
I want to write it in sql.

Thanks a million!
 
Lily said:
I have a table with a field "Price". and another table with Price range
"Price Low" and "Price High".
For example:
Table 1
Price Adj. Price
12
23
34

Table 2
Price Low Price High Relativity Low Relativity High
0 10 1 1.1
10 20 1.1 1.2
20 30 1.2 1.3
30 40 1.3 1.4
I do not want to use If then else clause to compare the price to range.
Because whenever I change table2 I will have to manual change the values in
if then clause.

I am wondering if there is a way to look up in table 2, if price is in one
of the range then I can assigh a value: Adj. Price to the corresponding price
in table 1: E.g. (Price High-Price)*Relativity Low +(Price-Price
Low)*Relativity High.

For example, 23 is between 20 and 30, then Adjusted price of 23 is
(30-23)*1.2+(23-20)*1.3.
I want to write it in sql.
Click to expand...


SELECT Price,
([Price High]-Price)*[Relativity Low]
+(Price-[Price Low])*[Relativity High] As AdjPrice
FROM [Table 1] INNER JOIN [Table 2]
ON Price Between [Relativity Low] And [Relativity High]

You can not specify this kind of join in the query design
window, so you'll have to work in the SQL view.
 
Thanks, Marsh. It works!
-Lily

Marshall Barton said:
Lily said:
I have a table with a field "Price". and another table with Price range
"Price Low" and "Price High".
For example:
Table 1
Price Adj. Price
12
23
34

Table 2
Price Low Price High Relativity Low Relativity High
0 10 1 1.1
10 20 1.1 1.2
20 30 1.2 1.3
30 40 1.3 1.4
I do not want to use If then else clause to compare the price to range.
Because whenever I change table2 I will have to manual change the values in
if then clause.

I am wondering if there is a way to look up in table 2, if price is in one
of the range then I can assigh a value: Adj. Price to the corresponding price
in table 1: E.g. (Price High-Price)*Relativity Low +(Price-Price
Low)*Relativity High.

For example, 23 is between 20 and 30, then Adjusted price of 23 is
(30-23)*1.2+(23-20)*1.3.
I want to write it in sql.
Click to expand...


SELECT Price,
([Price High]-Price)*[Relativity Low]
+(Price-[Price Low])*[Relativity High] As AdjPrice
FROM [Table 1] INNER JOIN [Table 2]
ON Price Between [Relativity Low] And [Relativity High]

You can not specify this kind of join in the query design
window, so you'll have to work in the SQL view.
Click to expand...
 
Back
Top