Aggregate Of An Aggregate In Report?

[JoeF]

I am running a report that has the following sort of layout. I am creating a report for a catologue type of business where there are several catologues sold by the company througout the year. Within each catologue there are a few different types of promotions (advertisements) used to advertise the products. My report shows how effective each promotion was over the season, as well as in each specific catologue. There are basically two groupings (headers in the design view of the report) in which I am creating totals:
Promotion Type Header Sums up all the sales statistics for a particular type of promotion across all Catologues.
Catolog Header Sums sales statistics the above promotion type within each catologue sold.
Detail List of specific items sold in a catolog on the specific promotional feature.

What I would like to do is run numbers for the promotion type as a whole, such as a straight average of promotion type performance in each catologue. However, I can't create a formula in the Promotion type header that aggregates data from the Catologue header.
For example, can I get the average of total catologue sales within a feature (avg of $50,000 and $40,000):
BUY ONE GET ONE PROMOTION

CATOLOGUE 1 TOTAL SALES $50,000
ITEM A SALES $10,000
ITEM B SALES $5,000
ITEM C SALES $20,000
ITEM D SALES $15,000

CATOLOGUE 2 TOTAL SALES $40,000
ITEM A SALES $5,000
ITEM B SALES $5,000
ITEM C SALES $15,000
ITEM D SALES $15,000

 

[JerryDennison]

You might try creating another query that will give you the averages based on the same criteria as your original query the report is based on. Make sure you include the group level field in this query's QBE grid along with the amount field. Change the query to a totals query and group by the group level fieldname and Avg the amount field. Close and save this query. Next open the report and modify it's recordsource property and add the query you just saved. Drag a join line between the group level fields and change it's property to include all records from the main table/query and only those matching from the query we just added. Drag the Avg field to the QBE grid then close and save. You now have available in your field list the Avg field. Add this field to your Group Header and you should get the results you're looking for.

 

[MichaelRed]

A variation of Jerry's approach would be to vreate Sub-reports. These would use the (main) report's recordsource as the souurce for their queries' recordsources and to the aggregate functions within te queries.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over