A Shipping Table that contains shipping information (unique records needs to be hooked up to an Rate table with > 100 records to find the Rate that should be calculated (used) for that shipment.
The following fields are in both tables:
Country, Carrier, Number of Pallets and Zipcode information.
The result from the shipping table is as follows:
RecordID Country Carrier N0 Pallets ZIP
1 DE DHL 10 20
2 FR GEO 5 41
The Rate table looks as follows:
RecordID Country Carrier N0 Pallets ZIP1 ZIP2 Charge
1 DE DHL 1 1 5 10
2 DE DHL 1 6 18 8
3 DE DHL 1 19 25 11
ETC
The rest of the table is till 33 pallets per country based on Zipcodes (different rate)
In mine example I want to look for the rate of recorded 1:
I already defined a unique Key based on Country+Carrier+ Number of Pallets but that still leaves more than 1 record to look for the ZipCode into Zip1 and Zip2 from the Rate table
How can I easy solve this problem? Help is more than appreciated!
What's the meaning of the two ZIP codes - is it a range (all ZIP codes inside the range get this rate) ? Is it a pair of "From ZIP - To ZIP" for the shipping origin and shipping destination ? Is it simply a choice of 2 zip codes, each one getting the same rate?
Depending on what it is, you need to modify your data structure and reflect the rules:
1. For a range, you can use INTERVALMATCH to link a single ZIP code to a range of ZIP codes (assuming that they are all numeric).
2. For a choice of 2 zip codes, simply reload your table twice ("normalize") to reduce the 2 fields into 1.
3. For a set of "from" and "to", you don't seem to have enough information in your transactions, so I'm not sure how to approach it...
But in any case, you need to determine the meaning of having 2 ZIP codes...
The two zip codes are indeed a range. Indeed a pair "FromZip1 to Zip2)All zip codes from the same carrier to the same destination are valid between the range. In the example the ZIP is 20 and should fall into the range of DE, DHL between 19 and 25. Outcome will be the 11 Euro's.
Basically all ZIP codes that are valid in that range are charged 11 Euro's
1.I tried the Interval Match, no result. But not sure why
2.Not an option,
3.It should be possible to match both tables and look into zip range for defining the last result.
