Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query for percents (Access 2000)

    I have to build a query comparing the old and the new prices and calculating the price increase.
    In order to see the percent of the price increase i divide the new price to the old price
    and the sum is divided to 100.Am i right and is it the right way to calculate the price increase?
    For example if the old price is 198,64 and the new price is 204,00 i get the result of 1.03 percent
    instead of 3 percent.In the field Format for the query, i have chosen percent.
    Secondly, some of the products have a 7 percent increase.How can i make the query show
    only those products that have 7 percent increase?


    SELECT NewPrices.Code, NewPrices.grade, NewPrices.oldprice, NewPrices.newprice,

    [NewPrices]![newprice]/[NewPrices]![oldprice]/100 AS Expr1
    FROM NewPrices
    ORDER BY NewPrices.grade;

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

    Re: Query for percents (Access 2000)

    1. To calculate the price increase, you must subtract the old price from the new price and divide the result by the old price:

    SELECT Code, Grade, OldPrice, NewPrice, ([NewPrice]-[OldPrice])/[OldPrice] As Increase
    FROM NewPrices
    ORDER BY Grade;

    Format the Increase column as Percent.

    2. Do you want to select products for which the price increase is exactly 7%, or where the price increase is between 6.5% and 7.5% ?

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for percents (Access 2000)

    Thank you so much for your nice answer.I see you are absloutely right how to calculate the percentage.
    You must know that your advices have helped me a lot.
    As to your question,i want to select products which the price increase is exactly 7 percent.How can i do it ?
    Thank you once again

    best regards

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

    Re: Query for percents (Access 2000)

    Since 7% is 7/100 = 0.07, you can use

    SELECT Code, Grade, OldPrice, NewPrice, ([NewPrice]-[OldPrice])/[OldPrice] As Increase
    FROM NewPrices
    WHERE ([NewPrice]-[OldPrice])/[OldPrice] = 0.07
    ORDER BY Grade;

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for percents (Access 2000)

    Dear Hans,

    Everything you have told me works perfect ! Thank you very much for that.
    You have asked me do i want to select products for which the price increase is exactly 7%, or where the price increase is between 6.5% and 7.5% ?
    When i answered you i didnt reckon with the fact that the calculations will give varying 7 percent increase, and therefore i will need the second alternative,
    i.e. between 6.5 % and 7,5 %. Would you apologize me for my misinformation and advise me
    how shall i proceed with the second alternative.

    Best regards

    Philip

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

    Re: Query for percents (Access 2000)

    Hi Philip,

    Use

    ...
    WHERE ([NewPrice]-[OldPrice])/[OldPrice] Between 0.065 And 0.075
    ...

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for percents (Access 2000)

    Dear Hans,
    somehow i cannot manage to make my query run with the 7 percent restrictions.Would you mind having a look at my query?

    Thank you

    regards

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

    Re: Query for percents (Access 2000)

    You formatted the Increase column as currency. If you format it as a percentage, you will see that the price increase is not exactly 7%, so you need the expression in my previous reply. See attached version.

Posting Permissions

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