FedEx Ship Zones 2

[bustersports]

I have downloaded the FedEx shipping zones from their website. They are based on zip codes, the 1st 3 digits. Example, a shipment going from 55531 to 85226 would be combined to be 555852 which would be a ship zone of 6. I have attached one of the files for you to look at. What I am looking for is a way to take all of the files, 708, be able to combine the zip code combinations and add the zone. The result would be a table that could be used for looking up the zone.

Here is what the data looks like to start. This is an example of shipments origination with zip codes 55300 - 55599, going to destination below. so 55530 going to 00500 would be combined into 553005, resulting in a zone of 6.

Destination Zip Zone
000-004 NA
5 6
010-011 6
12 5
013-051 6
52 5
53 6
54 5
55 6
56 5
057-067 6
068-083 5
84 6

What I would like to end up with, unless there is a better solution is:

Destination Zip Zone
000 NA
001 NA
002 NA
003 NA
004 NA
005 6
006 6
007 6
008 6
009 6
010 6

etc for all of the combinations. I hope this makes sense. Thanks for considering any options you are aware of.

FedEx does not offer the ability to download the files in any other format.

 

[lameid]

I see there are no takers to download the file. Perhaps if you posted a sample layout of the data, someone would help.

 

[JonFer]

You could split the FedEx data into DestFrom and DestTo zip fields and then use the Between criteria to find the zone. If a single destination value is listed, just copy it to the DestTo field as well. There should be an easily manageable number of rows.

 

[bustersports]

Lameid,

The top portion of the post is a sample of how the data is presented from FedEx in their spreadsheets. I am not sure how else to present. If you any suggestions on how else to explain it to make it more clear, please let me know. Thanks for taking a look.

Destination Zip Zone
000-004 NA
005 6
010-011 6
012 5
013-051 6
052 5
053 6
054 5
055 6
056 5
057-067 6
068-083 5
084 6

 

[bustersports]

JonFer,

Could you maybe please give me an example of how you are suggesting I handle this? My end result would be a table with all of the zones, unless you think another way is better. There are about 700 spreadsheets that would need to be ran thru whatever query it takes to get the data in a usable format. Thanks.

 

[JonFer]

Translate the Fed Ex data to this table format (FedExZones):
[tt]
DestFrom DestTo Zone
000 004 NA
005 005 6
010 011 6
012 012 5
013 051 6
052 052 5
053 053 6
054 054 5
055 055 6
056 056 5
057 067 6
068 083 5
084 084 6
[/tt]
Then your query would look like:

Select Zone from
MyTable, FedExZones
Where MyTable.MyZip Between FedExZones.DestFrom and FedExZones.DestTo

If you have multiple source zip codes that need translating, add SourceFromZip and SourceToZip (both 3 chars) or maybe just SourceZip (5 chars) depending on how many zips and how flexible you want it.

 

[lameid]

Now that I see Jonfer's solution, I understand the problem... I think Jonfer's solution is the way to go.

 

[bustersports]

Jonfer,

If I am correct, this solution will give me a lookup table, but does not split out each combination into a new record (ex: FromDest of 000 - 010 does not split out into 11 records). Am I correct? I am looking for a way to split out each one.

Thanks

 

[lameid]

I think I noticed a logic error in Jonfer's solution...

Assuming a 5 'digit' Myzip field...

Select Zone from
MyTable, FedExZones
Where MyTable.MyZip Between FedExZones.DestFrom and FedExZones.DestTo [red]& "99"[/red]

Moving on...

bustersports, why do you need to do it differently than Jonfer recommends? I ask because what you are asking to do is a lot more complicated and generally harder to maintain.

 

[bustersports]

Lameid,

The reason I am trying to get a complete list is for the user to be able to use drop down box. The zones do not change very often, as I was told that they have not changed for several years. Make sense or should I push back on the users to do it differently?

 

[lameid]

They can just inter the first three in two spearate comboboxes... I would think you would alread have the zipcodes in the data... Like a wharehouse address and shipto address... Not sure why a user needs to do anything other than enter the shipping information? Then you can return the zone.

 

[bustersports]

JonFer and Lameid,

Thanks for all your help. Got it now. Stars to both of you.