Finding minimum based on criteria of another field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcie - 07 Apr 2008 18:04 GMT

I have a report that is just doing calculations in the footer. The group is
based on a program.

Every footer counts, averages etc each program.

I can not seem to figure out how to do a min and max based on another field:
I have a calculation that counts part time and full time <35 = part time and

>35 equals full time.

Now I want to find the minimum and maximum pay for the above.
Below is how I get the part time hours
CntHrsPT=Abs(Sum([Hrs]>1 And [Hrs]<35))
I also am trying to find the median for pay, both full and part time.

Reply to this Message

John Spencer - 08 Apr 2008 13:38 GMT

Try one of the following

MaxPayPT = Max(IIF([Hrs]>1 and [Hrs]<35,[CurrentPay],Null))

or alternative
MaxPayPT = Max(Abs(([Hrs]>1 and [Hrs]<35) * [CurrentPay]))

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a report that is just doing calculations in the footer. The group
>is
[quoted text clipped - 11 lines]
> CntHrsPT=Abs(Sum([Hrs]>1 And [Hrs]<35))
> I also am trying to find the median for pay, both full and part time.

Reply to this Message