How to filter on a report

[kai]

Hi, All
I created a report group by broker, details are commissions on "dates
paid" and "amount" columns, then I have Broker footer show the sum on
"amount". I like to filter out all the brokers with sum=0 in the footer,
because commission can be positive or negative, so I have to filter on Sum
on the report.

Any help is greatly appreciated.

Kai

 

[Duane Hookom]

You may need to create a totals query that calculates your sum. Add this
query to your report's record source. You can join the broker fields and set
a criteria to filter out those with a sum of 0.

 

[kai]

Thanks.

Kai

You may need to create a totals query that calculates your sum. Add this
query to your report's record source. You can join the broker fields and set
a criteria to filter out those with a sum of 0.

 

[Marshall Barton]

I created a report group by broker, details are commissions on "dates
paid" and "amount" columns, then I have Broker footer show the sum on
"amount". I like to filter out all the brokers with sum=0 in the footer,
because commission can be positive or negative, so I have to filter on Sum
on the report.

You can not filter a report data by something that the
report must see the data in order to calculate whether the
report should not see the data (a circular argument). You
must calculate the filter value in the report's record
source query so the filter can be applied before the report
sees the data.

To do that, create a Totals query that only includes the
broker ID field and the total commission:

Query TotalAmounts:
SELECT brokerID, Sum(amount) As TotalAmount
FROM table
WHERE datepaid . . .
GROUP BY brokerID

Now you can modify your original report's query to only
select brokers with a non zero total amount:

SELECT . . .
FROM table INNER JOIN TotalAmounts
ON table.brokerID = TotalAmounts.brokerID
WHERE TotalAmounts.TotalAmount <> 0

 

[Kai]

Thanks a lot

-----Original Message-----


You can not filter a report data by something that the
report must see the data in order to calculate whether the
report should not see the data (a circular argument). You
must calculate the filter value in the report's record
source query so the filter can be applied before the report
sees the data.

To do that, create a Totals query that only includes the
broker ID field and the total commission:

Query TotalAmounts:
SELECT brokerID, Sum(amount) As TotalAmount
FROM table
WHERE datepaid . . .
GROUP BY brokerID

Now you can modify your original report's query to only
select brokers with a non zero total amount:

SELECT . . .
FROM table INNER JOIN TotalAmounts
ON table.brokerID = TotalAmounts.brokerID
WHERE TotalAmounts.TotalAmount <> 0