Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    percentage in a query (Access 2000)

    How can i express the percent margin in a query? I have a field PurchasePrice and a field Salesprice. the field Purchaseprice is the price at which we buy the products and the field salesprice is the field at which we sell the products.I tried to substract the salesprice to the pruchase price and divide the difference with the salesprice.
    Am i right and why the result in the query is not in percentage ?

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

    Re: percentage in a query (Access 2000)

    Set the Format property of the column in which you calculate the margin to Percent and if necessary, set Decimal Places to the value that you want.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: percentage in a query (Access 2000)

    Thank you. I did so but after formatting the reuslt is not right.I am sending the attachement. I didnt multiply by 100, how shalli do it ?
    Attached Files Attached Files

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

    Re: percentage in a query (Access 2000)

    In your table, the purchase price is higher than the sales price, which means that you're making a loss!
    Your expression is incorrect. If you want to compute the margin as a percentage of the purchase price, the expression to use is
    <code>
    ([SalesPrice]-[PurchasePrice])/[PurchasePrice]
    </code>
    And if you want to compute the margin as a percentage of the sales price, it is
    <code>
    ([SalesPrice]-[PurchasePrice])/[SalesPrice]
    </code>
    Note the use of parentheses around the subtraction. They are necessary because without the parentheses, division has precedence above subtraction so the calculation will not be correct.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: percentage in a query (Access 2000)

    Thank you so much, dear Hans ! It is all clear to me now !

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: percentage in a query (Access 2000)

    Accounting pedant here - Margin is the profit percentage based on selling price - Mark Up is the profit percentage based on purchase price. < end pedant>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: percentage in a query (Access 2000)

    Thanks, that's useful information (I am completely ignorant in this area, as you can tell...)

Posting Permissions

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