Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries (again!) (A2K)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    I always find it difficult to visualise my data as

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    I knocked up an example table.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Hokitika, West Coast, New Zealand
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    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.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    Dear Steve,

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

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    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.

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (again!) (A2K)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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