INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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]

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

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

Duane
Hook'D on Access
MS Access MVP

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

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close