Simple Report Questions

[burton99]

First I created a query that pulled only the information I needed to create a report. When run the query pulls 175 records (based on the dates I enter as criteria).
From this query I created my report, the report is based on these 175 records the query pulled. The entire report consists of calculations based on these 175 records.
When I run the report is gives me the same page report on 175 pages (the amount of records the report calculations were based on). Each page is EXACTLY the same. How do I avoid this problem. When running the report I want it to print only 1 page.

 

[RickSpr]

Presumably, your report's control source properties are using domain aggregate functions (DSum, DAvg, etc.) to come up with summary data on the whole recordset. However, it is the nature of a report to output one detail section for each record in its record source (subject to filtering).

The normal way to do this would be to build a query that contains only the totals, and thus contains just one record. That would cause the report to print only one page. You would base this second query on the existing query, but set the Field row to the expressions that calculate the aggregate values for the report. For example, you might set the Field row to (first column) "SumCol1: Sum(Col1)", (second column) "AvgCol1: Avg(Col1)", etc. Finally, you would change the report's Record Source to this second query, and change the controls' Control Source properties to the names you gave to the calculated columns in the queries (AvgCol1, SumCol1, etc.) Important: The query must NOT contain any actual columns of the original query; it can contain only aggregate functions on those columns.

There's another way you might try: Move everything in your report's Detail section to the Report Footer section. Set the Detail section's Visible property to False. Replace the control sources of all calculated controls to omit the aggregate functions. (Controls placed in the Report Footer section are automatically totalled.) This method is less efficient (because the report still has to process all 175 records; it just doesn't print the individual rows because the Detail section is invisible), but it may be easier for you at this point. Rick Sprague

 

[burton99]

Thank you VERY much for your help. I will try that and let you know how I make out. Thanks again.