×
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!
  • Students Click Here

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

Students Click Here

Jobs

Sum the Max

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!

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