Access 2003 VBA Help

[RobBhat]

Hi Frothingloss

The Freight Companies give us fixed costs for post code prefixes. It is not something that is calculated by the mileage. Its a bit like airfare. Even though some destinations are farther away, the cost may be a lot less than a closer destination. The weight is not a factor. The only factor is whether its a half or full pallet.

eg

For 0.5 pallet

Freight Route ADD Express
BD1 to BD11 £30 £32
BD12 to BD13 £33 £32
DE £38 £35

For 1 pallet

Freight Route ADD Express
BD1 to BD11 £40 £43
BD12 to BD13 £45 £45
DE £47 £46

 

[RobBhat]

My apologies Jdraw. I referred to you as Frothingloss

 

[jdraw]

No problem.

Does each Freight Company give you info in the same format?

Do you ship from your location? That is, do all your shipments start at your postal code?
Is there a relationship between .5 pallet and say 10 pallets? I thought there was also mention of different pallet sizes?

How often do the Freight Companies change their rates? How do you get informed?

Please clarify what exactly this means:

Freight Route ADD Express
BD1 to BD11 £40 £43
BD12 to BD13 £45 £45
DE £47 £46

I'm trying to understand why/how the different Postal code prefixes matter (if shipments start from your location). Perhaps you could give us an example of a 5 pallet shipment from your location to some "complex" Postal Code.

If ,in fact ,the postal codes are relative to your address, then I'd set up the rate table along this line. (may be separation of tables depending on formats). I think this is the basis of the query you'd need
"Which company has best(lowest) rate for X pallets shipped to RST Postal code."

Best express rate

SELECT tblRates.ShipTo, tblRates.StandardRate, tblRates.ExpressRate, tblRates.NumPallets, tblRates.FreightCompany
FROM tblRates
WHERE      ExpressRate =(select Min(expressrate) from tblRates WHERE ShipTo="bd36"  AND NumPallets =1 )

ShipTo	StandardRate	ExpressRate	NumPallets	FreightCompany
bd36	$58.00	$60.00	1	JohnCo

Or for best standardrate (similar PostalCode and pallets)

SELECT tblRates.ShipTo, tblRates.StandardRate, tblRates.ExpressRate, tblRates.NumPallets, tblRates.FreightCompany
FROM tblRates
WHERE      StandardRate =(select Min(standardrate) from tblRates WHERE ShipTo="bd36"  AND NumPallets =1 )

ShipTo	StandardRate	ExpressRate	NumPallets	FreightCompany
BD36	$57.00	$61.00	1	ABC Trucking

tblRates
RateID
ShipTo
StandardRate
ExpressRate
NumPallets
FreightCompany

with records such as

Id	ShipTo	StandardRate	ExpressRate	NumPallets	FreightCompany
1	BD1	$40.00	$43.00	1	ABC Trucking
2	BD5	$40.00	$43.00	1	ABC Trucking
3	BD21	$47.00	$51.00	1	ABC Trucking
4	BD36	$57.00	$61.00	1	ABC Trucking
5	bd1	$39.00	$44.00	1	JohnCo
6	bd5	$41.00	$44.00	1	JohnCo
7	bd21	$48.00	$50.00	1	JohnCo
8	bd36	$58.00	$60.00	1	JohnCo

There would lots of records for Rates, but these could be added to tables and I think would simplify the calculation.

Thoughts??

 

[RobBhat]

Hi Jdraw

The starting point is always the same
The charges are dependent on the destination prefix, the pallet size and whether its next day delivery or economy delivery. I Tried to give you an example, but the formatting was skewed. I have attached an example of the charges. As you can hopefully see, the charges are set by the freight company (based in the same town as our business) to all the different postcode prefixes. Usually, they are updated once a year.

If we do the tbl rates like this, there would be an immense number of inputs as the variables (upto 8 pallets in increments of 0.5 are huge). Do you think if we have separate table:

TblFreight company with one to many relationship with tblPostCodePrefixes with one to many relationship with TblCharges. TbleCharges will then have the fields: Pallets and charge. In this table there will be 16 inputs with charges. But, I dont know how this will cater for economy and Express?

 

[jdraw]

??In your example, I see the Price for 1 Pallet 74.83, and the price for 5 is 374.15 which is 5*Price for 1, so there is a functional relationship.
Price per pallet * number of Pallets.
And Price per .5Pallet is 64.14, Price for 1.5 Pallets is 138.97 which is
Price for 1 pallet + price for .5 Pallet.

So it seems you need price for .5 and Price for 1 pallet, other than that it seems to be a calculation

Price = (NumFullPallets* Price Per Pallet) +(numHalfPallets * Price per HalfPallet)

and for each FreightCompany

tblBaseRates would be reduced to

Company --PostCode --PricePerPallet, PricePerHalfPallet. (I didn't see standard vs express)

You would use that table and multiply the Rate *NumPallets

Maybe I'm missing something basic, but you don't need to store the Price for up to 10 pallets (or so) when the Price is just a multiple of the base price.

 

[RobBhat]

Hi JDraw

For Freight Route there is a functional relationship but not for ADD. And this complicates the issue you see.

 

[RobBhat]

View attachment PostCodeTest - Copy.mdbHi JDraw

I created a basic database along these lines. I got untangled at the end because when I came to create another Freight Company, its not allowing me due to Primary key issues. I have to go this morning abroad for a week. I will pick up your posts every now and then (Hopefully you will continue to help).

 

[jdraw]

With respect I think you are in the position of attempting to solve the overall issue without having a complete set of requirements. Perhaps, you know all the details, but that is not clear, at least to me yet. When I see "too many records" as part of why something is being dismissed or not pursued (which may be the correct direction), I'd like to see the full requirement first. And then identify the tables and relationships based on the business requirements. Build a model and test it with some sample data and business scenarios. This can lead to a blueprint for your development.

There is also an approach where you start at (or near) the end point. What exactly would you want/have to finalize an Order/Shipment to the Customer? Then go back one step, What exactly would you need in order to make that previous decision? You might say approaching the problem from the solution and working backwards. Sometimes this approach can identify missing data or clarify needs.

All this to say a clear and complete description of the business issue/problem/opporunity will help in resolving the puzzle.

I also see some changes in terminology -- which I take it to mean that Service Company and Freight company are one and the same. ADD is economy... little nuances that may mean the same to you, but seem to be new or altered terms. I suggest a clear statement of the business and related processes would be helpful to you and readers.

Good luck.

 

[RobBhat]

Hi JDraw...Sorry for the delay in replying. I just got back from my break...The purpose is this.

The customer places an order by phone. The staff have to create a delivery note for the warehouse to dispatch and the transport company to deliver. On the delivery note, we have a mainform and a subform. On the mainform we have:

The date
Customer name
Delivery address (need to select one of many for some customers)
The transport company name (We have 2 at present - Freight Route (FR) and Add Express (ADD))
The number of pallets
The Service type - Express or Economy
The cost of delivery offered by the freight company - The field we are trying to populate (Depending on the postcode prefix, the delivery company, the number of pallets and the service type)
The cost of delivery actually charged by the Freight company on their invoice (at a later date)

On the subform, we have the details of the goods to be delivered.

Why we need the delivery cost to be inputted? This is because, we need to compare the cost that they have offered against the actual delivery cost charged to us when they invoice us. Why is this important? It is because, they could overcharge us if we dont have a mechanism to compare the 2. This is why, we need the offered cost to automatically fill the field.

Rob

 

[jdraw]

Do you have an operational system?
Are you trying to design a means to record shipping rates by freight companies?
And a method to compare shipping charged against the registered shipping rates?

 

[RobBhat]

Hi JDraw
We have been running an access database for a few years now that we have been using. Besides this, there is no other electronic operational system for day to day administration.

You are correct. I am trying to adapt the current database to record shipping rates by freight companies to help us compare with the actual amounts charged for each shipment.

Rob