Do not display zero sum subtotals in a report.

[Guest]

My report shows securities buy and sell transactions. When a security is
bought then sold in equal amounts a zero SUM for QUANTITY results for that
security. In my report, I do not want to display securities with a zero SUM
for QUANTITY. I have two versions of the report, one that shows all
transaction history and one that only shows the sum. Is there a way to
accomplish this suppression in either the query or the report and what would
the specifics be?

Thanks,
Bruce

 

[Duane Hookom]

If I understand correctly, you might need to total the transactions within
the report's record source so the records can be filtered out.

 

[Guest]

Duane, that could be a solution. In the query, I can sort by security symbol
to sequence the records. The question then becomes, what do I need that will
sum records with the same symbol and on the basis of that sum being equal to
zero, exlude all the individual records from the query? The end result is
still to have a report that does not show securities that have a zero sum for
quantity.

 

[Duane Hookom]

Create another query that groups by security symbol and totals the quantity.
Add this query to your report's record source query and join the security
symbol fields. Set the criteria to the SumOfQuantity <>0.

 

[Guest]

Duane, thanks for the solution. It also gives me another dimension to think
in for future projects, i.e. the fishing rod instead of the fish.

Bruce

 

[Duane Hookom]

I had thought about walking you through some steps in solving this but I
don't have time for it. It's good to hear you are understanding something
that you can apply to other situations.