Summing only some of records on report

[fredk]

I have a report that has the following fields/results:

Status Total

Equal 10,000
More Health 15,000
No RX 5,000


What I want to do is only total the first two criteria (Equal and More Health) What is the most efficient way to get the total for only these two criteria on the report?

Thanks!!

Fred

 

[Darrylles]

Hi,

Only paint 'total' fields for these columns in your report,
with "=sum(fieldx)" as their recordsources.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[fredk]

Thanks for responding. I don't follow you. The field that I am totalling is [total] but I only want particular records to be included in the sum of [total] not all the fields in the detail section.

??

Thanks!!

 

[pdldavis]

Hi, you can do something like this in the same control source or in another text box set to invisible and with a running total set to accomplish what you want:

=iif([txtStatus]="No Rx",0,[txtTotal])

Hope that helps.

 

[fredk]

Thanks for the reply pdldavis - I did not know about the running total property that you mentioned!!! I am learning.

thanks very much!!!!

Fred

 

[fredk]

One other question for you pdldavis - I have the running total for each record now. How do I transfer this into a final total? My field is named "buffer" - I tried to use sum([buffer]) in the report footer but it does not recognize buffer as a field and asks me to input buffer?

Thanks!!!

 

[PaulBricker]

I don't think pdlDavis meant for you to use the running sum property for the textboxes. If all you want is a total for the two Status values then put this in a Textbox in your Report Footer or your Status Group Footer
=Sum(IIf([Status] = "Equal" or [Status]= "More Health",[Total],0))

That may do it. Or you can put a textbox in your Report Footer and set the control source to =RunningSumTextboxName
That may do it as well.

Paul

 

[fredk]

I had previously tried what you are saying and it did not work. Putting this in the status footer just give you and independent total for each status. In the report footer it seemed to have worked the first time I ran the report but when I re-ran it, it only gave me.

I tried it again and it seems to be working so I will test it a few more times.

Thanks for the help!

Fred