In Report Footer, total only Specific Group

[fredka]

I have a report that has a grouping based on a field named "BaseGroupStatus" The two values in this field are "basegroup" or "currentyearsale"

Under the grouping there is another field named status which has a few different values and then I am totalling a field named "sumofstartingmembers" which is in the detail. In the "BaseGroupStatus" footer I total which is just named text22.

I want to take the total of the "basegroup" field and put it in the report footer. I know I have done this in the past, however, I cannot get this to work.

I hope this makes sense - thanks!!!!

Fred

 

[genomon]

The two values in this field are "basegroup" or "currentyearsale"

I don't understand how you get two values in one field.
That aside, check out the DSum() function in Access help, and the FAQs in this site regarding running sums.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[dhookom]

I think DSum() will total records from a query or table rather than the report. You can open a report with a filter applied and the DSum() ignores the filter.

If you want to total FieldNum from a report where FieldText ="Some Text", your expression in the report's footer section would be:

=Sum(Abs([FieldText] ="Some Text") * [FieldNum])

Duane
Hook'D on Access
MS Access MVP

 

[fredka]

I actually just figured this out today. I am not sure if this is the best way to handle it, however, it does work. In the grouping footer (basegroupstatus) I added a field that has the following control source:

=iif([basegroupstatus]="basegroup",sum(sumofstartingmembers])

This field is named "persistency"

I actually hid this field on the report. Then in the report footer I just referenced the field above (persistency). I was tryng to sum the above field but I just needed to reference it since it was summing all the sub-categories for the "basegroup".

The field is needed to calculate a "persistency" number which is based only on the basegroupstatus of base

Just for clarification, my data looks like this:
BaseGroupStatus = Base (grouped)

Status StartMembers CurrentMembers Increase/Decr
Active 100000 98000 -2000
Terminated 39000 -39000

BaseGroupStatus = NewSale (grouped)

Status StartMembers CurrentMembers Increase/Decr
Active 59994 63994 4000
Terminated 3811 -3811

I hope this makes sense. Thanks so much for the replies!!

Fred

 

[dhookom]

I'm more confused following your description since you seem to be confusing "fields" with "controls". A field is a column in your report's record source. A control is a text box or similar in your form or report. You provided some sample records but didn't show the report total section.

Duane
Hook'D on Access
MS Access MVP

 

[fredka]

Sorry.... I guess I am confusing the terminology in my description.

However, my conrols are named the same as my fields. For example, I have a field in my query that is named "status"... the text box in the report is also named "status"

But I see what you are saying regarding the custom controls that I mentioned above. I should have said that I have a control in the "basegroupstatus" footer that has the following control source:

=iif([basegroupstatus]="basegroup",sum(sumofstartingmembers])

this control is named "persistency"

I then added a control in the report footer that has the following control source:

=[persistency]

The purpose of all of this was to only get the total for one value of the "basegroupstatus" field.

I hope that makes sense and I did not confuse things more. I just wanted to thr and explain what I did. I get a lot of help from searching previous posts so I know it is important.

As always, thanks for your help!!!

Fred