access 2003 filter a report

[Bob Perry]

In a Grouped Summary Report, how do I filter the records shown by sum for
each group? Example: Sums vary from 1 to 20; I want to show in the report
only those groups with a Sum of 10.

 

[Allen Browne]

You cannot filter a report on a value that is not calculated until after the
report has run. To filter it you will need to get the totals into the source
query somehow.

One approach is to use a DLookup() expression in the query that feeds the
report. This will be slow.

It may be possible to use an aggregate query (i.e. depress the Total button
on the toolbar in query design.) You may find that this totals query does
not give you the details you need, and so you need a subreport to show the
details.

Another possibility is to use a subquery. Here's an introduction:
http://allenbrowne.com/subquery-01.html
However, filtering on a subquery and then grouping a report is likely to
give you 'multi-level group-by not allowed' errors, so may not be
satisfactory.

 

[Bob Perry]

Thank you for the information.

You cannot filter a report on a value that is not calculated until after the
report has run. To filter it you will need to get the totals into the source
query somehow.

One approach is to use a DLookup() expression in the query that feeds the
report. This will be slow.

It may be possible to use an aggregate query (i.e. depress the Total button
on the toolbar in query design.) You may find that this totals query does
not give you the details you need, and so you need a subreport to show the
details.

Another possibility is to use a subquery. Here's an introduction:
http://allenbrowne.com/subquery-01.html
However, filtering on a subquery and then grouping a report is likely to
give you 'multi-level group-by not allowed' errors, so may not be
satisfactory.