Is it possible to use access calculate freight costs

I need to audit the freight bills charged by freight company which has
over 20,000 consignment per week. The basic charge is base on distance
and weight, something like this:
SYD-MEL <1kg $6
SYD-MEL<3kg $6.7
SYD-MEL<5kg $7
SYD-MEL<10kg$10
SYD-MEL>=10kg $12, $4 per kg rate
SYD-PER<1kg $10
......

Based on the freight rate, how can I use Access to calculate the
freight charge for each of the consignment ? Is there anyway to build
some query or something to retrieve the freight rate and calculate the
freight cost of each consignment?

Thanks

Re: Is it possible to use access calculate freight costs

Yes. I have written a sales and distribution system which does exactly
that. The charges are normally (for Australian freight companies at
least) made on a base cost plus per kilogram basis, with weight
"breaks". I assume you are sending ex Sydney. For each freight
company, you need to capture their charging rules in tables, for
examples suburbs, postcodes, weight breaks, zones, parcel types, and
perhaps volume. If you're sending via Australia Post they have
specific sizes for large letters, small letters etc. that determine
the costs. If you're dealing with a single freight company, it will
be a little easier. To reconcile charges by the freight company, you
will need to match the line items on the freight companies invoice -
typically sent by the freight company in soft copy such as Excel - to
your despatch records. You calculate what you reckon the charges
should be and compare it to the freight company's invoice line
items.. This should be done in a "managed" way - keep a record of all
freight invoice data in structured tables, and allow for adjustments
and comments to be made. Your accountant may have control of this. I
suspect you may also need to have your despatches designed in a
structured way - typically one or more "pickups" per day.
You would not normally attempt to do this with queries alone, you'll
need to code VBA, and it's not trivial.
hope this helps
Terry Bell
On Nov 24, 7:38 pm, whn...@gmail.com wrote:
> I need to audit the freight bills charged by freight company which has
> over 20,000 consignment per week. The basic charge is base on distance
> and weight, something like this:
> SYD-MEL <1kg $6
> SYD-MEL<3kg $6.7
> SYD-MEL<5kg $7
> SYD-MEL<10kg$10
> SYD-MEL>=10kg $12, $4 per kg rate
> SYD-PER<1kg $10
> .....
>
> Based on the freight rate, how can I use Access to calculate the
> freight charge for each of the consignment ? Is there anyway to build
> some query or something to retrieve the freight rate and calculate the
> freight cost of each consignment?
>
> Thanks