Sum Max IIf 1

[traycee]

I'm not even sure if this is possible...but I've been working on it all day and need assistance. I have a query that sums the miles by program. Example:

Jane Doe
Program Miles
ABC 232
MATX 102
GHI 5
JKL 30

I can easily get my report to show the maximum value out of the four programs for each worker. (Each worker may have one or more programs with varying amounts of mileage in each program per month.) Here's the kicker. Some workers will have MATX miles, some will not, and some have MATX miles but no other program miles. I need to add the MATX mileage to the program with the highest amount of miles for each individual worker. In the above example that would be (232 + 102) but if MATX has the highest amount of miles then I need to add it to the program with the NEXT highest amount of miles. If the worker only has MATX miles...then I just need it to show.

Is this even possible?

 

[dhookom]

You could try a text box in the worker footer with a control source like:
=Max(IIf([Program]="MATX",0,[Miles]))+Max(IIf([Program]="MATX",[Miles],0))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[traycee]

Thanks. It's simple and it works great.

One more question if I may. In the Worker footer I still need to show each of the other programs that may be listed for each worker....but not MATX or the program with the Maximum value since they are already added together in the footer.

 

[dhookom]

Try:
=Sum(Abs([Program]<>"MATX") * [Miles])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]