Calculate Ave/Max/Min of Group Subtotals

[Deeb1234]

Hi:

I created an Access 365 report that displays job-to-date cost information. It is grouped by ProjectName, and it successfully subtotals the sub category records within each project in the ProjectName footer. My ultimate goal in the report header would be to display the average, maximum, and minimum $ values of each group as shown in the picture.

What I've tried so far is to create a text box for each value in the report header. For example, to calculate the average of all the subtotals, I've tried =Avg(Sum([JTDCost])) as the Control Source formula, but I get "Cannot have aggregate function in expression Avg(Sum([JTDCost])))." Is it even possible to do what I'm attempting? Thanks for any insights or other options I should try.

[davegri]

Conceptually, isn't avg(JTDCost) same as avg(sum(JTDCost)) ?

[Welshgasman]

All I can think of is hidden textboxes in the report footer that hold the calculations and refer to those in the header.

Hard to produce header totals when no data has been produced, as the header is written first.

[Deeb1234]

Avg(JTDCost) will calculate an average of all the 5 records ($255,094), but it is the average of the totals ($345,395, $247,154, $682,921) that I'm looking for ($425,157) .

[mike60smart]

Hi
Are you able to upload a copy of the Database?

[Deeb1234]

Unfortunately, the database contains a lot of proprietary information.

[mike60smart]

Hi
Ok Then all you need is in the ProjectName Footer add an Unbound Control to Count(ProjectName) and name it txtCount

Then in the Report Footer add an Unbound Control and Sum([NameofTotal in ProjectName Footer])/[txtCount]

[kd2017]

Sounds like you're after the avg, min, max of the results of a subquery. There's many different ways to approach this.

This first step might be to just create aggregate query like so:


Step two: Then use the DAvg(), DMin(), DMax() functions in your report header textbox's control source. The domain parameter for these functions would be the name of your query.

I assume the report is based on a filtered set of projects. Your query from step one can reference your report or a form or whatever and apply a where clause, or you can pass a filter to the domain functions from step 2.

(EDIT)
See attached for example database

[Deeb1234]

Mike, thanks for your reply. At your suggestion, I did add an unbound text box control to the ProjectName footer with a Control Source of =Count([ProjectName]) and named it txtCount. The name of my Total in the ProjectName footer is MyJTDCost. In the report footer, I also added an unbound text box control with a control source of =Sum([MyJTDCost])/[txtCount]. When I view the report, however, I get prompted to enter a parameter value for MyJTDCost. Thoughts?

[mike60smart]

Hi

When you get asked for a Parameter it normally means a spelling mistake. Check the name of the Total Control in the ProjectName Footer.

[Welshgasman]

Unfortunately, the database contains a lot of proprietary information.

We do not need all the db, just enough to see the problem.
To hide the proprietary information, review this post https://www.accessforums.net/showthread.php?t=82910&page=2&highlight=randomize# 19

Make sure it is on a copy of your DB. Comapct and zip, and upload.

[Deeb1234]

Hi, kd2017:

I think the example database you put together is on the right track of what I'm trying to do. I'll take a closer look to see if I can make that work with mine. Thanks so much for your time and effort!

[mike60smart]

Hi

I added a Form to record all costs for a Project
Then using kd2017's example I added a Report to show all required values.

[Deeb1234]

I cannot thank everyone enough for all the input I've received on this post. Between the suggestions and example databases contributed, I now have a way forward to accomplish what I set out to do. Now it's just a matter of finding time to work on implementing those ideas. THANKS EVERYONE!