INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Sum the Max

## Sum the Max

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

     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 

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

### RE: Sum the Max

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)

### RE: Sum the Max

(OP)
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.

### RE: Sum the Max

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]

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

Then in the report:
in the footer where you had the max function, change its control source to the new field
MaxDept
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
=Sum(MaxDept)

### RE: Sum the Max

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

      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


### RE: Sum the Max

My suggestion was based on your layout in the original post. You seem to have added an additional col.
     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)


### RE: Sum the Max

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

### RE: Sum the Max

(OP)
Worked great, thanks!!

### RE: Sum the Max

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

### RE: Sum the Max

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

### RE: Sum the Max

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

### RE: Sum the Max

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

### RE: Sum the Max

(OP)
Worked great, thanks!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!