Setting up rate tabels depending on postcode and customer for a haulage company

[AdamHope]

I am looking to set up some rate tables in MS Access database. I have been banging my head for a few days now.

I will try and explain this as best i can.

The main table is where jobs are put on, you select a customer, select a collection address from a combo box(linked to a collection table), select a delivery address from a combo box (linked to a delivery address table), (which auto drops in the postcode, site restrictions ect) and you select how many pallets will be deliverd to that destination.

I want to set up a rate table for the price to charge the customer depending on the from and to postcode. Depending if part or the whole postcode is entered into the postcode list.
I want to assign rates to customers, If there are no rates set up for that particular customer then it will use the default rates.

I want a postcode list table
(blank "----" cust is default)

-------------------------
| CUS | Posccode | Rate |
-------------------------
| A001| BA | 111 |
| ----| BA | 222 |
| ----| BA12 | 345 |
| A001| CO10 3PZ | 398 |
| ----| CO10 3PZ | 222 |

Rate Table
-----------------------------------------------------
| Rate | RateName | Min pallet | Max pallet | Price |
-----------------------------------------------------
| 111 | Sched 1 | 0 | 2 | £15 |
| 111 | Sched 1 | 3 | 10 | £14 |
| 222 | Sched 2 | 0 | 2 | £18 |
| 345 | Sched 3 | 0 | 5 | £20 |
| 398 | Sched 4 | 0 | 5 | £22 |
| 398 | Sched 4 | 6 | 10 | £19 |

As you can see i have set up bands depending on how many pallets are delivered. The more they have delivered the cheaper it becomes.

So if i was delivering 1 pallet from cus Z056 to postcode BA14 3PN it would be rate 222 between the 0 and 2 min max values and be £18 per pallet.

If it was delivering 3 pallets from cus A001 to posctcode CO10 3PZ it would be rate 398 between 0 and 5 min max values and be £22 per pallet.

How do I tell the main table to look up these values and store them in the field with so many different dependancys? I belive it is with "if" and "dlookup" statements but just keep goin in circles now!!?
I need it to be updated if the address is changed, and also if the amount of pallets is changed.

I have code for the after update of the address combo box. Alot of the commented marks are me playing around on a trial and error basis!

Private Sub Combo74_AfterUpdate()
Me!DPostCode = Me!Combo74.Column(1)
Me!Deliver2 = Me!Combo74.Column(2)
Me!dsiterestrict = Me!Combo74.Column(3)
 
Dim strPostCode As String
Dim varCharge As Variant
'#############################################
'######Look up and store the rate number######
'#############################################
    varCharge = Null
    strPostCode = Nz(Me.DPostCode.Value, "")
    If Len(strPostCode) > 0 Then
    'PostCode field isn't empty
    If InStr(strPostCode, " ") Then
    'Trim off the in-code
    strPostCode = Left([strPostCode], 2)
    End If
    'Look up and store the RateNo
    varCharge = DLookup("[DelRSNo]", "[RatePostcodes]", "[Postcode]='" & strPostCode & "'")
    'Set the value of the Rates box
    Me.sched.Value = varCharge
    Me.Rate.Value = Nz(DLookup("[Rate]", "RateDetail", "[RateNum] = " & varCharge), 0)
 
If Format(Now(), "ww") >= 0 <= 14 Then GoTo Deldays_Week_0_14
If Format(Now(), "ww") >= 15 <= 28 Then GoTo Deldays_Week_15_28
If Format(Now(), "ww") >= 29 <= 52 Then GoTo Deldays_Week_29_52
 
Deldays_Week_0_14:
    Dim strPostCodeArea As String
    Dim varDeliveryCharge As Variant
    varDeliveryCharge = Null
    strPostCodeArea = Nz(Me.DPostCode.Value, "")
    If Len(strPostCodeArea) > 0 Then
    'PostCode field isn't empty
    If InStr(strPostCodeArea, " ") Then
    'Trim off the in-code
    strPostCodeArea = Left([strPostCodeArea], 2)
    'Select postcode characters to the left
    'strPostCodeArea = Left(strPostCodeArea, _
    InStr(strPostCodeArea, " ") - 1)
    End If
    'Look up the postcode
    varDeliveryCharge = DLookup("[EstDelDay]", "[tblEstDelDays1]", "[Postcode]='" & strPostCodeArea & "'")
    'Set the value of the combo box
    Me.EstDelDay.Value = varDeliveryCharge
 
Deldays_Week_15_28:
    Dim strPostCodeArea1 As String
    Dim varDeliveryCharge1 As Variant
    varDeliveryCharge1 = Null
    strPostCodeArea1 = Nz(Me.DPostCode.Value, "")
    If Len(strPostCodeArea) > 0 Then
    'PostCode field isn't empty
    If InStr(strPostCodeArea1, " ") Then
    'Trim off the in-code
    strPostCodeArea1 = Left([strPostCodeArea1], 2)
    'Select postcode characters to the left
    'strPostCodeArea = Left(strPostCodeArea, _
    InStr(strPostCodeArea, " ") - 1)
    End If
    'Look up the postcode
    varDeliveryCharge1 = DLookup("[EstDelDay]", "[tblEstDelDays2]", "[Postcode]='" & strPostCodeArea1 & "'")
    'Set the value of the combo box
    Me.EstDelDay.Value = varDeliveryCharge1
 
Deldays_Week_29_52:
    Dim strPostCodeArea2 As String
    Dim varDeliveryCharge2 As Variant
    varDeliveryCharge2 = Null
    strPostCodeArea2 = Nz(Me.DPostCode.Value, "")
    If Len(strPostCodeArea2) > 0 Then
    'PostCode field isn't empty
    If InStr(strPostCodeArea2, " ") Then
    'Trim off the in-code
    strPostCodeArea2 = Left([strPostCodeArea2], 2)
    'Select postcode characters to the left
    'strPostCodeArea = Left(strPostCodeArea, _
    InStr(strPostCodeArea, " ") - 1)
    End If
    'Look up the postcode
    varDeliveryCharge2 = DLookup("[EstDelDay]", "[tblEstDelDays3]", "[Postcode]='" & strPostCodeArea2 & "'")
    'Set the value of the combo box
    Me.EstDelDay.Value = varDeliveryCharge2
End If
End If
End If
End If
End Sub
 

If there is anything i have missed out or not explained properly please let me know,

Thanks for taking the time to look at this,

Hope you can help :)

[Stewart Ross]

Hi. It seems to me that you've set yourself somewhat of an impossible task; you want to be able to set up a rate table which defines the rate given the collection and delivery postcodes of a delivery. However, the possible combinations of postcodes involved run into billions (there are approx 1.8 million postcodes in the UK, so think of approx (2x10^6)^2 combinations).

In any event, your postcode element is only a proxy for the distance involved - and it is the distance which should in the main determine the rate.

Given the use of a postcode database - such as the Postzon data Royal Mail licences commercially on an annual basis - you would have access to the easting and northing (the x and y co-ordinate) of every address in the database. Given the to- and -from address eastings and northings it is possible to compute the distance between them as a straight-line approximation, and base your price bands on the distance travelled.

More complex approaches using sat-nav type road databases could give you actual distances between two postcodes, but at root the same technique is being used: compute the distance between nodes on the network, not work out all possible combinations of start and end postcodes to devise rate tables.

In the end, you want to keep the look-up task simple and maintainable. A rate table does not have to be complex, and should not require a battery of VBA code to make it work.

-Stewart

[AdamHope]

Hi Stewart, Thanks for your reply,

I think that this is a good idea. I do have some code that plots the co-ordinate's to my address by linking to microsoft mappoint and saves these in the table along side the address, but am unsure how i would get it to calculate the distances between 2 points in Access.

Would i still be able to set up different costs depending on the customer? Also Would still need the price bands depending on pallets delivered (Gets cheaper the more they want delivering).

I wanted to set up rate tables like this because this is how they are set up in another ms access database i have seen this done similar to this - i just havent seen the code behind the forms :(

Your help is much appreciated,

Adam

[Stewart Ross]

Hi. The differences between the to- and from-address eastings and northings tell you (in km) how distant the two points are from each other in the two directions (east-west and north-south). You need to understand what eastings and northings mean - they are distances in km from an arbitrary point located (if I remember) to the south-west of England, and define how far east and north all other reference points are located. Ordnance Survey (amongst others) have reference material on this on their web site. So do Royal Mail, who explain also to what accuracy their eastings and northings are quoted - in the least-expensive cases it is to the nearest 100m.

It is possible to convert these from rectangular co-ordinate form to polar co-ordinates (from x, y to distance and angle) which gives you the distance directly. You can find the details on how to convert rectangular to polar co-ordinates by looking up any suitable reference source (e.g. Wikipedia). Such approximations are fine for computing distance for your needs - you don't need to look into the complexity of converting eastings and northings to true latitude and longitude, for instance, which is sometimes necessary for mapping purposes.

You should have a separate volume discount table to store the business rule that specifies the default discount for the number of palates bought. This is just a table storing the thresholds at which you apply particular discounts. For example, if 20 palates would trigger a 20% discount and 40 palates 30%:

Threshold Discount
      0         0
     20       0.2
     40       0.3

-Stewart