Multiple Calculations in a Query

[MiTortuga]

I need help with access2000!!!!

I have a table with cost and revenue by contract. 1 contract can have multiple revenue sources. I created a query to sum total revenue by contract. That works fine. I also want to calculate gross profit (gp) dollars and gp% based on my sum total revenue field and it wont’ work.

Someone told me that I can’t perform a calculation on a calculated field and suggested I use the make-table function in my query to refresh my data into a new table. Then base a 2nd query on the new table and perform my calculations. My gp dollars calculations works, but when I do the second calculation, the % don’t tie out. What am I doing wrong? Is there a restriction on the # of calculations performed in a query?

Thanks

 

[Pat Hartman]

You can do as many calculations in your query as you want. But some calculations are best done in reports and a % calculation is one of them. If you want to calculate % in a query, you actually need two queries. One that counts and one that sums. You can then join the two in a third query and calculate the %.

Someone told me that I can’t perform a calculation on a calculated field

They were probably referring to controls on forms or reports. For example if you have a controlSource such as:
=Sum(fldA + fldB)
and you want to do something with that. You need to repeat the calculation. You can't say:
=Avg(YourCntl)
but you can say
=Avg(fldA + fldB)