Lookup / Join on interval

  • Thread starter Thread starter lunde
  • Start date Start date
L

lunde

I have a table orders where I want to select a carrier based on the
delivery_country and pack_weight. To do the latter I have defined a new
table freight_rules with this content:

country weight carrier class
Germany 500 carr1 mail
Germany 2000 carr2 pack
Germany 10000 carr3 courier

So what I'm after is to lookup/join where orders.delivery_country =
freight_rules.country and orders.pack_weight <= weight. The later gives
me problems, for how can I make for example a pack_weight of 350 grams
pick "carr1" and a pack_weight of 1500 grams pick "carr2" ?

Thanks
Mogens
 
The syntax is far easier if you have the two limits of your range:


....
FROM myTable AS t INNER JOIN rules AS r
ON t.country = r.country
AND t.pack_weight >= r.minWeight
AND t.pack_weight < r.maxWeight



Note the use of < to avoid a given weight to belong to two categories. You
should also care that there is no hole, neither overlap, in data of your
table.



Hoping it may help,
Vanderghast, Access MVP
 
lunde said:
I have a table orders where I want to select a carrier based on the
delivery_country and pack_weight. To do the latter I have defined a new
table freight_rules with this content:

country weight carrier class
Germany 500 carr1 mail
Germany 2000 carr2 pack
Germany 10000 carr3 courier

So what I'm after is to lookup/join where orders.delivery_country =
freight_rules.country and orders.pack_weight <= weight. The later gives
me problems, for how can I make for example a pack_weight of 350 grams
pick "carr1" and a pack_weight of 1500 grams pick "carr2" ?
Click to expand...


If you want the carrier/class for every entry in the Orders
table, then you can use something like:

SELECT O.OrderID, O.pack_weight, O.delivery_country
R.carrier, R.class
FROM [freight_rules] As R INNER JOIN Orders As O
ON R.country = O.delivery_country
WHERE R.weight = (SELECT Min(X.weight)
FROM [freight_rules] As X
WHERE X.weight >= O.pack_weight
And X.country = O.delivery_country)

OTOH, if you want to find the carrier/class for a single
order that being created through a form:

SELECT TOP 1 R.carrier, R.class
FROM [freight_rules] As R
WHERE R.weight >= Forms!theform.pack_weight
And R.country = Forms!theform.delivery_country
ORDER BY R.weight ASC
 
Back
Top