Calculate shipping costs by zip code

[joecamel9166]

I have been trying to find some info on how to go about doing this, but came up empty.

I downloaded some tables form UPS.com. one table includes shipping zones with destination zip codes Low and High(using the first 3 digits of the zip code to determine the zone) so what I need to do is take the zipcode I entered into my ShipTo table and extract the first 3 numbers, then match it up against this table.

then I need to take that zone and match it up against the rates table. So I have the weight pre determined dependent on the selected model in my main table. So by the weight and zone I need to extract the cost.


If anyone has done anything like this before some help would be greatly appreciated.

This should be my last great hurdle, then this project will be done.



Thanks!

[davegri]

Something's missing here. I don't see any way that tblShipZones can point to any column in tblGroundShipRates.

[joecamel9166]

I don't think they can be related.
I'm trying to come up with a string that puts together all the criteria that I need and then do a dlookup to the rates table.

but it has to be done multiple times, because shipping can be ground(default), 2day, and NextDay

[JoeM]

I think you may find it very difficult, as your rates table is not normalized, which makes it very difficult to work with.
An example of how this creates difficulty is with the DLOOKUP formula, you need to tell it which field to return. But you cannot say, because it really depends on the value (do you need to return Zone2, Zone3, etc. ?)

In a normalized structure. you would only have one rate column. So instead of having 10+ fields your different rates, you would have a Zone Number column, and a Zone Rate column. So each record would only have one rate. So you would have less fields and more records.

Check out this link for a good discussion regarding the Rules of Normalization in Table Design: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf. You usually want to get out to at least the Third Normal Form. If you don't, you will often find that various tasks and calculations are much harder then they have to be. A simple example of this is what if someone said that their shipping rate was $12.47 and wanted to know which zone that rate can be found in. In your current structure, you would have to search 10+ fields to find it, whereas if your data table was normalized, it is a simple search of one field.

[joecamel9166]

Joe,
There is no active link. I did a little digging, I can see the problem there.
I do need every zone, because the shipping prices need to be accurate.
I also need to have the weight, because the price is based on weight and zone.

Should I make each zone a separate table

tblZone002:
ID(PK)
Weight
Price

tblZone003
id(PK)
Weight
Price

etc.

[JoeM]

The link actually brings up a PDF file (I just clicked on it to confirm that it is working). Here is another link that includes information of the Rules of Normalization. Following these rules creates a design which maximizes your ability to perform tasks as easily as possible. If you don't follow them, you can program yourself into corners and create a nightmarish situation for yourself (I have been there and done that).

I am not saying to get rid of any data, just to restructure the tables into a normalized format. You do NOT want a separate table for each zone. If you have multiple tables structured similarly, that is another sign that your data is not normalized. You should have one table, structured like this:

ID
Weight
ZoneNumber
Price


So your data would look something like this:

ID   Weight   ZoneNumber	Price
1	1	2		$6.94
2	2	2		$7.66
3	3	2		$7.78
4	4	2		$8.03
5	5	2		$8.07
…	…	..		…
21	1	3		$7.58
22	2	3		$8.25
23	3	3		$8.94
24	4	3		$9.40
25	5	3		$9.85
…	…	..		…

[joecamel9166]

Ok Cool. Thank you!
Ill start restructuring.

As for my other question at the top, Any idea of how to pull the first 3 digits out of the zip code. it needs to be >= to DestZipLow and <=DestZipHigh.

[joecamel9166]

and yes the link works. I am reading through it right now. It didn't appear when I first saw that post.

[JoeM]

It didn't appear when I first saw that post.

I actually forgot it initially, and then went back and re-added it a few minutes later. You must have seen it before that edit of mine.

Any idea of how to pull the first 3 digits out of the zip code.

You can use the LEFT function to pull the first three digits off a zip code. If you then add zero, it will coerce that value to a number (so "003" would become 3), i.e.

LEFT([ZipCode],3)+0

When using greater than or less than, it always work better if you are working with numbers rather than text.

[joecamel9166]

Thank you. Your help is much appreciated!

[JoeM]

You are welcome! Glad I could help!

[joecamel9166]

Sorry to have to ask again, Can you take a look at this code and let me know what is wrong with it?
The strZip and first If statement worked fine yesterday, but after I added the second string and if statement, nothing works.
(I'm not very versed in writing code so I'm guessing there is something wrong with it)

Dim strZip As String
strZip = Left(Me.CustZipShip, 3)


If strZip >= DLookup("DstZipLow", "tblShipZones") & strZip <= DLookup("DstZipHigh", "tblShipZones") Then
Me.Ship_Zone.Value = DLookup("Ground", "tblShipZones")
Else
End If


Dim strShippingCost As String
strShippingCost = "Zone =  '" & Me![Ship Zone] & "'and Weight= '" & Me!Wieght & "'"
If Me.Ship_Via = "UPS Ground" Then
Me.Ship_Cost.Value = DLookup("Cost", "tblGroundShipRate", "strShippingCost")
Else
End If

[JoeM]

If you are trying to include two clauses in your IF statement, they need to be joined by AND, not "&" (which is actually concatenate), i.e.

If (strZip >= DLookup("DstZipLow", "tblShipZones")) And (strZip <= DLookup("DstZipHigh", "tblShipZones")) Then

Also, did using strZip as a string work? Greater than or less than does not always work like you think with Text values. For example, if you had text values from 1 to 12, they would sort like this:

1
10
11
12
2
3
4
5
6
7
8
9

So, you might need to do it like this:

Dim strZip As Long
strZip = Left(Me.CustZipShip, 3) + 0

If (strZip >= DLookup("DstZipLow", "tblShipZones") + 0) And (strZip <= DLookup("DstZipHigh", "tblShipZones") + 0) Then
    Me.Ship_Zone.Value = DLookup("Ground", "tblShipZones")
End If

[JoeM]

Actually, now that I think about it, you may be all right because you are maintaining the leading zeroes in your number, so I think the order will be fine, i.e. the first 12 formatted as "000" would sort like:

001
002
003
004
005
006
007
008
009
010
011
012

So I think you are fine using strings, because all your text entries are exactly the same length and have leading zeroes. If they did not, then you would have the issue I mentioned in my previous post.

[redbull]

I have been trying to find some info on how to go about doing this, but came up empty.

I downloaded some tables form UPS.com.

Would you be opposed to just manipulating the UPS website to get the rates?