Rate table lookup

[Sammy]

How do I approach creating a shipping rate lookup
application? I have 3 tables:
Table 1 "Countries" including fields: country & zones
(each country is 1 zone). Table 2 "Rates" including
fields: zones, pounds, & rates (one zone corrsesponds
to many countries). Table 3 "Shipments" including
fields: pounds and country. I can't figure out how to
plug in a country and a weight from the shipments table
and get the rate. If this is complicated, is there a
sample I can look at somewhere?
Thanks!

 

[Michel Walsh]

Hi,


I assume table2, Rates, has data like:

Zone, Pound, Rate
1 0 1.50
1 5 .25
1 10 .10
1 25 -.05


which we read: in zone 1, from 0 to 5 pounds, the rate is 1.50 per pound,
from 5+ to 10 pounds, the rate is an additional .25 per pound (1.75, if
you prefer),
from 10+ to 25 pounds, an additional .10 per pound (for a total of
1.85), and for anything higher than 25 pound, the rate is 0.05 less ( 1.80
per pound, if you prefer).

So, if the item weight 7 pounds, the rate is 1.50 for the first 5 pounds,
plus 1.75 for the last 2 pounds:

5*1.50 + 2*1.75 = 11

For 18 pounds, that would be:

5*1.50 + 5*1.75 + 8*1.85 = 31.05



So,

SELECT SUM(a.rate*( [Weight]-a.pound )) As TotalRate
FROM Rates As a
WHERE [Weight] >= a.pound AND a.zone=1


would give the total rate, given the parameter [Weight] (and the zone=1).

Indeed, for Weight=18, the SUM does:

1.50 * (18 -0) + .25 * ( 18-5) + .1 *(18-10) = 31.05, as
expected.



Now, to "link" it with Countries, to indirectly select the right zone, it
just a matter to do something like: