False Sum when using "Hide Duplicates" field option. 1

[edwilli]

I have a report that displays revenue for courses we offer, and sums the total. However some of these courses are taught in two locations, which causes them to be displayed twice. This is okay because I want to view both locations on the report. For revenue data I use the "Hide Duplicates" option to make it invisible. Unfortunately the Revenue Sum Total picks up these hidden values and adds them to the total. Is there a way I can remove the hidden values from the Sum Total?

Example:

Course1 LocationX $1,000
Course2 LocationX $1,000
(Course2) LocationY ($1,000) (Parens indicate hidden values)
Total: $3,000

$3,000 is the wrong amount. I want it to display $2,000.

Any help is appreciated. Thanks.

Eric

 

[GShen]

never used this hidden value feature before. But can you ascertain which fields you want to add (Eg. LocationX)? If so you can put this in the control source with and iif condition (IIF is the correct syntax).
Something like
=iif([field] = "locationx", locationx,0)
This will make all the location x values themselve and everything else a 0.

Hope this helps.
If it is what you want and you are still having problems,
respond with more detailed info. (eg. actual field names, etc).

 

[dhookom]

Hiding duplicates only makes the control invisible. Its value is still there and is summed into your expression. You would need to do something with a group header. You would group on the level of detail that you _want_ to sum.

You could then add a text box in the header that uses a running sum on the value. Name the text box something like txtRSRevenue.

In your report footer, add a text box with a control source of:
=txtRSRevenue
The group header and text box can be very tiny and not allowed to grow. The text box could be hidden.

Duane
MS Access MVP

 

[edwilli]

Thank you dhookom!! I was able to replicate what you suggested with success. One detail I would like to ask you though: The running sum only works if you display it in the report footer. I would like to have it in the report header. Any suggestions?

Eric

 

[dhookom]

If you want it in the report header, you may have to create the query to calculate the value. This may take a couple group by queries or whatever you are comfortable with. You should be able to get to a query with one record. Add this record to your reports recordsource or use DLookup() in your control source.

Duane
MS Access MVP