Hi MarkUsing a single rate per row...
Hi MarkHey Bob,
To implement the solution I proposed you need to do a numeric sort on the Weight field, so making it a string doesn't make sense. You can see from what CJ is saying that a numeric sort on a string adds cost without payoff.
EntryID | OriginCountry | DestHongKongChina | DestAustraliaNewZealand | DestRestOfAsia | DestEurope | DestAmericas | DestRestOfWorld |
---|---|---|---|---|---|---|---|
1 | China | 0.90 | 1.62 | 0.90 | 0.90 | 0.90 | 0.90 |
2 | Hong Kong | 0.90 | 1.65 | 0.90 | 0.90 | 0.90 | 0.90 |
3 | Rest Of Asia | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
4 | Europe | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 |
5 | Americas | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 |
6 | Rest Of World | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 | 0.20 |
Origin | Destination | Rate |
China | Hong Kong / China | 0.90 |
China | Australia /New Zealand | 1.62 |
China | Rest of Asia | 0.90 |
China | Europe | 0.90 |
China | Americas | 0.90 |
China | Rest of World | 0.90 |
Hong Kong | Hong Kong / China | 0.90 |
Hong Kong | Australia /New Zealand | 1.65 |
Hong Kong | Rest of Asia | 0.90 |
Hong Kong | Europe | 0.90 |
Hong Kong | Americas | 0.90 |
Hong Kong | Rest of World | 0.90 |
Rest Of Asia | Hong Kong / China | 0.00 |
Rest Of Asia | Australia /New Zealand | 0.00 |
Rest Of Asia | Rest of Asia | 0.00 |
Rest Of Asia | Europe | 0.00 |
Rest Of Asia | Americas | 0.00 |
Rest Of Asia | Rest of World | 0.00 |
Europe | Hong Kong / China | 0.20 |
Europe | Australia /New Zealand | 0.20 |
Europe | Rest of Asia | 0.20 |
Europe | Europe | 0.20 |
Europe | Americas | 0.20 |
Europe | Rest of World | 0.20 |
Americas | Hong Kong / China | 0.20 |
Americas | Australia /New Zealand | 0.20 |
Americas | Rest of Asia | 0.20 |
Americas | Europe | 0.20 |
Americas | Americas | 0.20 |
Americas | Rest of World | 0.20 |
Rest Of World | Hong Kong / China | 0.20 |
Rest Of World | Australia /New Zealand | 0.20 |
Rest Of World | Rest of Asia | 0.20 |
Rest Of World | Europe | 0.20 |
Rest Of World | Americas | 0.20 |
Rest Of World | Rest of World | 0 |
Public Function GetShippingRate(Origin as string, Destination as string) as currency
GetShippingRate = dlookup("Rate","NormalizedTable", "Origin = '" & Origin & "' AND Destination = '" & destination & "'")
end function