Circular Reference in Report Footer

[iamdanno]

Hello All,

I have a report which shows how a construction estimate is broken down into 16 different divisions of work. It's data source is a select query with calculated fields to total the cost of individual items, all of which have a quantity, labor unit cost, and material unit cost. The report header shows the division field and the sum of it's item totals (which it totals from the detail section). So I get a list of the divisions with their corresponding totals. The report footer gives me a TOTAL of all the divisions. Perfect. What I want to do is add a contingency, overhead, and profit to the TOTAL and then calculate a GRAND TOTAL for the entire estimate. The problem is that I need to calculate the contingency, overhead, and profit as a percent of the GRAND TOTAL not the TOTAL. Whenever I try to do this I create a circular reference and get #ERR as my text box value. I know Excel can do this, but is it possible in Access 2000?

 

[Andrew Fergus]

Hi, what are the percentage figures you are trying to use for the contingency, overhead and profit?

[Edit - I have since worked out how to do this]

You can work out the percentages based on the total cost (but using a base other 100% such that the effect is as if you calculated it based on the grand total) as follows :

Assuming profit = 10% of grand total, contingency = 10% of grand total and overhead = say 20% of grand total, then use the following :

Profit = [Total Cost]*(0.1/(1-0.4))
Contingency = [Total Cost]*(0.1/(1-0.4))
Overhead = [Total Cost]*(0.2/(1-0.4))
Grand Total = [Total Cost] + [Profit] + [Contingency] + [Overhead]

You will need to adjust the calculations for your percentages and where I have used "1-0.4", you need to replace it with 1 - x, where x is the sum of the 3 percentages.

HTH, Andrew.

 

[iamdanno]

That's great! It never occurred to me that plain old algebra would solve that. I appreciate your help.