1. ## calculating volume (a2002)

Hi,

I am sure that I have asked this question before or have read it posted by somebody else but I have searched all of my own posts but I cannot seem to find it.

I have a form on which I need to calculate whether boxes are charged by weight or volume, the formula adopted by the airlines is L x W x H divided by 6000 if the answer is more than the boxes actual weight it becomes the chargeable weight.

On my form I have the following fields
LxWxH = Total
L1xW1xH1 = Total1
L2xW2xH2 = Total2

each of the totals properties are set to =L*W*H and another box, total volume has =([total+total1+total2])/6000, this works fine but is cumbersome. Could somebody please give me a bit of advice on the following

1. Could I automate this in a better way
2. Could I somehow link it to another box on the form that has 'total pieces' so that the user must input the correct amount of dimensions
3. I know that most people say you should not store calculated values but as the Customer may at a later date query the charges
(because we have charged them on volume and not actual) where should the dimensions be stored?
4. There could in theory be hundreds of different box sizes, I have used just 3 on my form though because of space limitations.

I hope this makes some sense

Thanks

Steve

2. ## Re: calculating volume (a2002)

Steve,
I'm not sure I fully understand the problem, but let me make an interpretation, and suggestion based thereon: I think you want to ultimately compute a grand total charge for multiple boxes, where the box count could be one, or some number greater than three, where the charge on each box is computed on the greater "charge-unit" of either weight, or the volume computation shown.; something like an Invoice.

If the interpretation is correct, what I would do is provide a form command button that indicates a new box to be added to the total. The action of the button would be to launch a new form ( giving it the cover "InvoiceID") that would have input text boxes for the new box weight, length, width and height, and a command button to "Continue". Form code behind the "Continue" button would validate the four input fields, archive the four fields, along with a "BoxID" and "InvoiceID" in a table, and compute the box charge. This charge would then be returned to the underlying form via a Global variable, and used as part of the total charge.

This technique allows a fully open-ended solution, to handle any number of boxes provided to the shipping invoice (assuming we're talking about a shipping invoice), and keeps an audit trail of all boxes placed on the invoice.
If I've misunderstood the problem, then perhaps you can expand on it.
-- Jim

3. ## Re: calculating volume (a2002)

Hi Jim

Thanks for the response. My level of expertise means that this wil probably never be linked to an accounts module, however your basic interpretation is correct. To clarify it a little more I have made a small snapshot of the process I am trying to do, this is part of an A4 sized page.

If you could offer any advice I would appreciate it.

Thanks

Steve

4. ## Re: calculating volume (a2002)

Instead of having three instances of length, width and height in the main table, create a separate table linked to the main table by VolumeID. Each record in the separate table is a box. You would present these records in a subform on the main form. Calculations would be mostly carried out in queries.

5. ## Re: calculating volume (a2002)

Thanks Hans,

I will try to do this, a quick question though

This 'snapshot' was a small portion of a larger form which was too big to post. I need this to happen within the form, can I therefore place a query inside this form?

Thanks

Steve

6. ## Re: calculating volume (a2002)

I don't know what "place a query inside a form" means. You can place a subform inside a form.

7. ## Re: calculating volume (a2002)

Sorry, I always seem to have trouble saying what I actually mean!!

If you send out a courier shipment on lets say DHL or Federal Express you need to complete a consignment note. The way that they arrive at the consignment charge is by calculating whether or not the shipment volumes, the formula they use to do this is to multiply l x w x h and divide the total by 6000, if the result is greater than the actual weight, the volume weight becomes the chargeable weight, if not the actual weight is the chargeable weight. I thought that I could perhaps do this within the form itself but obviousely I am barking up the wrong tree.

On one consignment it is possible to have many boxes with many different dimensions, this means that I need to ability to be able to tell my query how many boxes there will be so that it will know when the total calculated = total actual boxes.

I have followed your example of the 2 tables and tried to make a query based on them without much success, I also tried to use the expression builder to get an answer in the chargeable weight box by doing the following

if [actualweight] > [volumetricweight] then [chargeableweight] = [actualweight]
if [actualweight] < [volumetricweight] then [chargeableweight] = [volumeweight]

Assuming that I can get this working would I then have this as a subform to my main form so that when it is printed it will show the actual and the chargeable weight?

Hopefully this may explain things a little better.

Thanks

Steve

8. ## Re: calculating volume (a2002)

Steve,

I've attached a small example mdb file which shows the general idea of what I spoke of above. As you'll note, it is nowhere complete, and needs much tuning. But, it should give you an idea as to one technique for creating the "many to one" type of construct, and how to display the results. Others may have equally good ways of proceeding.

-- Jim

9. ## Re: calculating volume (a2002)

Thanks for your time Jim, I shall have a play.

Cheers

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•