Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation from tables (2000)

    Hi Guys,
    I'm having this problem with an access database in relation to calculations. Its been really bugging me for weeks.
    Yet, it does seem simple. It involves calculation. The below is what i'm trying to do.

    1. There are two forms that are of concern, frmQuotation and frmManifest,
    on frmManifest, there are are 2 textfields, Weight and Amount. The Amount is calculated according to the Weight,
    The calculation table is stored in frmQuotation (tblQuotation & tblZoneRates). Now looking at the frmQuotation, we have
    Zone, Type, 1stWeight, 1stRate, AddWeight, AddRate, Surcharge.
    These fields refer to the calculation process for each zone, eg ADL is Adelaide. Type means Parcel or Document (P or D), 1stWeight
    means if it weighs less then 0.5g, it will be charged at $15 (1stRate), any subsequent weight value of 500grams after that would add $14.
    And the end result would be output into the amount field in the frmManifest. This all depends on the zone, as you can see, each zone has a special price.
    By default, all rates for shipments would be calculated from the PublishedRates2003 account.
    However, if the customer account (QuotationID is customeraccount) has a different rate for a specific zone, then that would override the default rate.
    If you can help me out, it would be soo appreciated. I'm ripping out my hairs, aagghhh.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation from tables (2000)

    I'm <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. How do I get the Zone and Type information for a record in tblManifest? The Destination field contains numbers for some records, text for others, there is also a Terminal field, which is mostly empty, and Content is mostly empty too.

    Oh, and by the way, I assume that you mean 0.5 kg, not 0.5g.

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation from tables (2000)

    Zone in tblManifest would be retrieved from tblZoneList, it could also be added into frmManifest if need be. In frmManifest, 'Content' is the same thing as 'Type'.
    Apologies for the confusion in the data, I was experimenting with queries to see which works best. Only to leave me baffled.
    As my design flaw has lapsed and need assistance on it. Please help me.
    yep, your right, 0.5kg is correct.
    Regards
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation from tables (2000)

    Please clean up your design before asking us to solve your problems. You shouldn't expect us to do the cleaning for you. So:
    - Make Destination into a number field, and populate it with valid values.
    - Decide whether you want to store Type/Content as numbers (1 or 2, as in tblManifest) or as text ("D" or "P", as in tblZoneRates); be consistent and modify the design and data accordingly. Provide a valid value for Content in all records in tblManifest.
    - Remove database objects not needed for this problem. qryManifestCN can't be opened in this version, neither can frmServiceZone, so scrap them.
    - Try to remove other inconsistencies.
    Then repost the database.

Posting Permissions

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