Lookup / Join on interval

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
 
M

Michel Walsh

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
 
M

Marshall Barton

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
 
Top