Sum the Max 1

[mtownbound]

Just want to sum the max values (max([Tickets])) of all groups:

[pre] IT Dept Tickets
Thomas Smith 200
Mary Johnson 250
Ken Allen 150
IT Footer Ticket Max: 250

Finance Dept Tickets
Brian Smith 115
Mark Lind 37
Linda Carrol 85
Finance Footer Ticket max: 115[/pre]

I need to sum the "Ticket Max" from each group in the report footer. In this case, 250 + 115 or:

Total Summed Ticket Max: 365

 

[sxschech]

If the textbox containing [Tickets] is named the same as the field, change it's name such as to txtTickets, then in the Footer that textbox would have a formula =max(me.txtTickets)

 

[mtownbound]

Thanks for the response. The max function is working for the group footers with no problem, but I need a sum of those group footer max values in the report footer.

 

[sxschech]

Had trouble figuring it out and maybe there is a better way, but ended up with this...

[Change the field and table names in the example below to your actual names]

Instead of doing the max in the report, add a dmax field to your query.

[pre]MaxDept: DMax("nz(Tickets,0)","YourTableName","Emp='" & [Emp] & "' AND Dept='" & [Dept] & "'")[/pre]

Then in the report:
in the footer where you had the max function, change its control source to the new field [pre]MaxDept[/pre] Treat it as a normal field, no function on it.
in the footer where you want the sum of the max, change the control source to [pre]=Sum(MaxDept)[/pre]

 

[mtownbound]

Getting there, it's pulling the correct max value but it's now adding the max value to each row so it's essentially:


[pre] IT Dept Tickets MaxDept
Thomas Smith 200 250
Mary Johnson 250 250
Ken Allen 150 250
IT Footer Ticket Max: 250 New Ticket Max: 750

Finance Dept Tickets MaxDept
Brian Smith 115 115
Mark Lind 37 115
Linda Carrol 85 115
Finance Footer Ticket max: 115 New Ticket Max: 345
[/pre]

 

[sxschech]

My suggestion was based on your layout in the original post. You seem to have added an additional col.
[pre] IT Dept Tickets
Thomas Smith 200 <---This would refer to the ticket field from your query
Mary Johnson 250
Ken Allen 150
IT Footer Ticket Max: 250 <---This would refer to maxdept from your query

Finance Dept Tickets
Brian Smith 115
Mark Lind 37
Linda Carrol 85
Finance Footer Ticket max: 115


Total Summed Ticket Max: 365 <---This would be the formula =Sum(MaxDept)
[/pre]

 

[dhookom]

Add a text box txtMaxRun I the group footer and set properties
Control source: =Max(YourFieldName)
Running Sum: Over All
Visible: No

Then add a text box in the report footer:
Control Source:=txtMaxRun

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

Worked great, thanks!!

 

[mtownbound]

Update on this thread, I also need to pull name of the person that had the max value in the group footer.

 

[dhookom]

So based on your data, which names would you expect to see where?

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

In is case it would be Mary Johnson with 250 and Brian Smith with 115.

 

[LaurieHamlin]

DLookup("EmpName", "YourTableName", "Tickets = " & txtMaxRun.Value & " And Dept = '" & deptFieldName & "'

 

[mtownbound]

Worked great, thanks!