Totalling

[cbrsix]

I have attached a screenshot of the report I am trying to create. My problem occurs when I try and create subtotals and totals for the AG and RA columns. These AG and RA columns are formatted as Yes/No columns in their respective table. I have $ values listed for each states AG and RA in another table. I have the report set up to insert the $ value instead of the check mark created by the Yes/No format. I can not get these to total at all. All it does is count the records of AG and RA instead of totalling the $ values.

[pbaldy]

Try

=Sum(Your IIf function here)

but use 0 instead of "" for the false argument.

[cbrsix]

Thank you. That worked.

[cbrsix]

I came across another problem. I can't seem to get the Grand Total working. I was able to get each column AG and RA to total in each group and then was able to create a single total (Rep Total) from the group subtotals. You will see, in the screenshot, that the Rep Total =[Text75]+[Text76] these both are the subtotals from each group. I want to have a Grand Total at the end of the report aggregating the Rep Totals. Any ideas?

[pbaldy]

Typically it would be done the same way as the group footer. If you don't want the two extra text boxes, you can either hide them or in your total text box:

=sum(IIf 1) + sum(IIf 2)

[cbrsix]

I want to keep the subtotals under AG and RA for each Rep and also have the Rep Total. How do I create a grand total at the end of the report totaling the Rep Totals?

[cbrsix]

Sorry, misunderstood what you were saying. I got it to work. Thanks.

[pbaldy]

No problem, glad you got it sorted out.

[cbrsix]

Sorry about reopening this thread (I promise this is the last time). But now I have run into a problem where I have the column [Firm AG Fee] that I don't want to show on my report except for down in the Report Footer as a subtotal. In the table, there is a fee for each state registered as AG in. In my report, it is summing each state for each rep. I just want it to sum the column from the table not for each rep as shown in the report. Any ideas?

Thanks.

[pbaldy]

I'm not clear on the problem. A control source of

=Sum([Firm AG Fee])

should total the amounts, regardless of state.

[cbrsix]

It says there is a Control Source problem (see attached). Also attached is a clip of the table.

An example of what it is doing is

Group Rep1
State
NJ 50
PA 10
Group Rep2
State
NJ 50
VA 15

The total should be 75 but it is returning 125 because it is summing NJ as many times as it appears in each group.

I hope this helps.

[pbaldy]

The circular reference is probably because the textbox has the same name as the field. If you're saying the data is repeated in the report's source query for some reason, you may have to use a DSum() to sum the data directly from the table, or use a subreport.

[cbrsix]

The circular reference is fixed. Attached shows the Dsum function. Is it set up right? It is returning a #error when going to report view.

[pbaldy]

No, it's not. This should help:

DLookup Usage Samples

You may also need to bracket the field name due to the inadvisable spaces.

[cbrsix]

I entered this:
=DSum([Firm AG Fee],"States","ID= ID") and it returns a number that is closer to the actual amount but don't know why it is still off. Is this still set up wrong?