Queries (again!) (A2K)

Topic

New Reply

Hi,

I have posted this message (or similar versions) a few times and have had a few different responses (thank you) I have been trying the various suggestions for about 3 weeks now and cannot seem to make any headway. Perhaps I have not explained in enough detail what it is I need to do, so here goes.

In the freight industry we take bookings from Customers over the telephone, I am trying to produce a form that can take down the details which will then be sent to various forms that we have to print. One of the most important things are obviously the charges, these are based on the actual weight or the volumetric weight of the goods. To determine this we have to calculate the volume of each carton and then compare it to the actual weight, which ever is the greater is then the chargeable weight.

There is no standard size of box so I cannot make a table and have box A for example at 50 x 50 x 50 cms etc., a consignment can consist of several boxes (or unpacked goods such as machinery, goods in sacks etc.).

A typical scenario may be that a customer calls and says he has 5 boxes weighing 100 kilos, 2 measure 50 x 50 x 50 cms, 1 measures 40 x 50 x 60 cms and the remaining 2 measure 100 x 70 x 70 (to determine the volume weight each dimension has to be multiplied and then divided by 6000). The total volume for this consignment would be 143 kilos, as this is more than the actual weight the volume weight becomes the chargeable weight.

Currently I use calculated fields on a form, which works OK, but as I have to make allowances for the fact that there may be many different size boxes I have to break the normalisation rules and have fields repeated (i.e. Box, Box1, Box2 etc.). Also the amount of space taken up on the form is horrendous.

Secondly, because the information needs to be eventually distributed through several forms (Master Awb, House Awb, Customs Documents, Shipping Invoice, Sales Invoice etc.) I need to store it. To make matters worse Government law states that import and export records have to be kept for 7 years, I therefore need to store the actual dimensions as well as the totals.

I have tried phrasing queries until my fingerprints have been rubbed out but I cannot see the wood for the trees.

I hope this makes a little more sense and will enable somebody to point me down the right path.

Thanks

Steve

Reply | Quote

Replies

I always find it difficult to visualise my data as

Reply | Quote

I knocked up an example table.

Reply | Quote

Hi Rupert,

Thanks for taking so much time for the answer and the example, I was out yesterday so I will take a look today.

Thanks once again

Steve

Reply | Quote

If you are using separate fields for each box you will run into problems – do you have a maximum number of boxes allowed?

Have you tried putting the information for the boxes into a separate linked table and a subform? There will no limits on the number of boxes. You can create a total in the subform for the volume weight and use it on the main form to compare that with the actual weight.

And you could use whatever information is relevant on each report.

Reply | Quote

Hi Lorraine,

Thanks for the response.

There is no limit to the number of boxes that we can receive. Imagine that you are moving home and have ‘scavenged’ all of your packing boxes from local stores, it is probable that none of them will be a standard size and you could end up with dozens of different dimensions. In reality over many years in my experience there will probably be a maximum of about 15 different sizes.

I am trying to get my head around Ruperts explanation at the moment, very very slowly.

Thanks again

Steve

Reply | Quote

Dear Steve,

If your happy with the general idea of putting the boxes in a table called

Reply | Quote

BTW,

You use the formula:

Volume weight = each dimension multiplied and then divided by 6000

Out of interest, where does the 6000 come from? Does it relate to dimensions in cm or will it work for all scales, feet, yards etc.

Reply | Quote

Hi Rupert,

Thanks for the further information.

Many years ago the airlines realised that companies sending a ton of pillows taking up a lot of space were paying the same rates as a company that was exporting say a ton of lead, taking up a small amount of space.

Together they came up with the formula that 6000 cubic centimetres would be equal to 1 kilo, this is true through most of the world except most African carriers that use 5000 cc = 1 kilo. If the dimensions are input in inches the formula is 366 cubic inches = 1 kilo, although people rarely use inches these days.

Thanks again

Steve

Reply | Quote