Sum of sum problem

[henkus]

This seems like a strange problem that really should work, but don't know why it doesn't...

I have a report that's based on a query. The query uses group by functions. Short example:
select Itemgroup, count(*) as TotalNr, sum(Turnover) as SumTurnover
from Campaign_det
group by Itemgroup;

The report the has the column names in the report header, and the column values in the detail field.
What I want to do is to display the total sum of all itemgroups for the columns TotalNr and SumTurnover. Thought it should be possible to just add a textbox with controlsource =SUM([TotalNr]. If I add it to the footer, I just get "Error" in the textbox. If I put it in the detail field, the value is correct, but is displayed on every row. What am I doing wrong?

/henkus

 

[ajetrumpet]

here is an example of what you're trying to do. The syntax is different. Look at the QRY, and RPT objects.