Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Circluar Reference (Access 2000)

    Edited by HansV to present data in table format.

    Is it possible to create a circular reference in access? I know that it is possibel in excel but not sure if you can do it in Access.

    Ex: I am tring to calculate the Standard Margin; Standard Margin= (Selling Price- Std Cost)/ Selling Price

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Std Margin</td><td>Std Cost</td><td>Selling Price</td><td align=center>2</td><td align=right>0.3</td><td align=right>5</td><td>=(A3*C3)+B3</td></table>
    Is it possible to calculate the selling price this way in access?

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

    Re: Circluar Reference (Access 2000)

    I think you meant =(A2*C2)+B2, otherwise it is not a circular reference.

    The answer is no, you can't do this in Access. You'll have to find some way to calculate the result directly.

  3. #3
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Circluar Reference (Access 2000)

    If you're calculating the Standard Margin, why do you have the formula in the Selling Price cell? I don't see anything in your description prior to the table that would necessitate a circular reference in either system.

  4. #4
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Circluar Reference (Access 2000)

    That was in correct statement by me, I know the standard margin, and the standard cost, I am tring to find the selling price. Is there anyway to link it to excel to retreive the information?

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

    Re: Circluar Reference (Access 2000)

    You don't need Excel. From the formula

    Standard Margin= (Selling Price - Std Cost) / Selling Price

    you can derive an explicit expression for the Selling Price:

    Selling Price = Std Cost / (1 - Standard Margin)

    You can use the expression Std Cost / (1 - Standard Margin) in a query, for example.

  6. #6
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Circluar Reference (Access 2000)

    Thanks!!! I can't believe I didn't think of that.

Posting Permissions

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