access 2003 filter a report

  • Thread starter Thread starter Bob Perry
  • Start date Start date
B

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.
 
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.
 
Thank you for the information.

Allen Browne said:
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.
Click to expand...
 
Back
Top