Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculations (A2002)

    Hi all

    Having sorted out my parameter query I am faced with another challenge. I have a tariff that has 9 zones and 40 weight increments 0.5 - 20 kilos, each of the weight breaks has a set rate i.e. zone 1 0.5 =

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

    Re: Calculations (A2002)

    I would add a dummy record to the tblExpressCourierRates table with the increment; for instance for weight = -1.

    The rate now becomes a calculation instead of a straight lookup. It isn't useful to have the parameters in the query any more, I would do all calculations in the form. This is the expression to calculate the rate. It looks daunting, but if you split it into components, you will see that it just looks up the various components of the calculation in the query:

    =IIf(<font color=4682b4>[cboWeight]>20</font color=4682b4>;<font color=red>DLookUp("Rate";"qryExpressCourier";"Coun tryID=Forms!frmCountry!cboCountry And Weight=20")</font color=red>+<font color=448800>([Forms]![frmCountry]![cboWeight]-20)</font color=448800>*<font color=blue>DLookUp("Rate";"qryExpressCourier";"Cou ntryID=Forms!frmCountry!cboCountry And Weight=-1")</font color=blue>;<font color=magenta>DLookUp("Rate";"qryExpressCourier";" CountryID=Forms!frmCountry!cboCountry And Weight=Forms!frmCountry!cboWeight")</font color=magenta>)

    See attached database.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations (A2002)

    Hi Hans

    Do you dream in code as well?

    Thank you it wil probably take a week for me to work it through so you lot will have a holiday from me. One thing I did notice though was that I changed rateband 2 to the actual Australian rates in the tblCountry changed Australia to band 2 and ran the form the result however was not what I expected. For example 25 kilos should return
    Attached Files Attached Files

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

    Re: Calculations (A2002)

    Oops, forgot to explain. The value for the dummy weight of -1 is the increment per kilogram, so you should use twice the half-kilogram rate - 7,60 for band 2.
    If you prefer to enter the half-kilogram rate in the table, you must change the expression that computes the rate.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations (A2002)

    Hi Hans

    I though I had understood exactly what you had done but in practice it proved to be otherwise.

    I have another table for and economy tariff that increases by 1 kilo from 10 - 50 kilos and then after that they go up by a fixed sum per kilo. Originally these rate bands were A - G but as I tried to emulate your example I figured it would be better to number them and changed them from 10 - 17.

    I made sure that (I think) everything was pointing in the right directions but it still failed to work I have changed the > than weights to take account of the different weight structure and made sure that all references about express were changed to economy and the after updates were in place.

    I am baffled and would appreciate your help once again, although it is important that it works it is doubly important that I understand how it works and to implement these things (It will save me bother you in the future!)

    Cheers

    Steve
    Attached Files Attached Files

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

    Re: Calculations (A2002)

    Hi Steve,

    In qryEconomyCourier, the tables should be joined on the fields that correspond to each other: EconomyBand (since you're dealing with economy rates now) and RateBand, not on ExpressBand and EconomyCourierID, those fields have nothing to do with each other.

    In the control source of txtRate and txtEconomyTransit on the frmEconomy form, you still refer to frmCountry. You should refer to frmEconomy instead, because that is the form that contains the controls.

    Don't forget to adapt the instruction for weight on this form.

    Extra note: the field EconomyCourierID in your tblEconomyCourierRates table serves no practical purpose, and it doesn't make sense to have the combination of EconomyCourierID and Weight as primary key. The primary key should be on the combination of RateBand and Weight, since that uniquely determines the rate. This is not essential to make your form work, however.

    I haven't attached a modified database this time; you should be able to implement the necessary modifications yourself.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations (A2002)

    Hi Hans

    As usual, right on the money, I actually enjoyed doing it because I understood what I was doing and having it work gives you a good boost.

    One thing I got slightly confused on and perhaps misunderstood what you were saying was

    __________________________________________________ ___________________________________________

    Extra note: the field EconomyCourierID in your tblEconomyCourierRates table serves no practical purpose, and it doesn't make sense to have the combination of EconomyCourierID and Weight as primary key. The primary key should be on the combination of RateBand and Weight, since that uniquely determines the rate.
    __________________________________________________ ___________________________________________

    In each weight band there were 41 possible weights i.e. band 17 weight 10 kilos, band 17 weight 11 kilos etc it meant that these bands and weights could not be unique so I left that part as it was and it works.

    Is it working ineffeciently though, and did I miss something?

    Cheers as usual


    Steve

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

    Re: Calculations (A2002)

    Hello Steve,

    The RateBand field by itself is not unique, and neither is the Weight field, but the combination of these two is, or should be unique: you can't have two records with RateBand = 17 and Weight = 11. That's why I suggested using the combination of the two fields as primary key. This would make looking up values in the table slightly more efficient, but as I noted, it is not essential; things should work OK if you leave it as it is.

Posting Permissions

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