Results 1 to 6 of 6
Thread: Circluar Reference (Access 2000)

20060105, 19:31 #1
 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?

20060105, 19:33 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060105, 22:31 #3
 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.

20060106, 15:46 #4
 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?

20060106, 15:54 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060106, 18:16 #6
 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.