pulling the a record by greatest value of a field

[Ted]

Hi all,

I know i've done this before but for some reason i'm having problems here.

if i have 2 records:

Policy_Number Rating Identification MaxOfPremium Amount
RSA000128-07-00 1 00000058
RSA000128-07-00 3 00001251


how can i pull out the record w the highest premium? If i run a group by
query it groups by Rating Identification so it won't give me the record i
want. i want to pull the record w 3 as the Rating Identification. any ideas
would be very appreciated. its driving my crazy.

TIA
Ted

 

[strive4peace]

Hi Ted,

use this as criteria in the [MaxOfPremium Amount] field

--> dMax("[MaxOfPremium Amount]", "[TableOrQueryName]",
"Policy_Number='" & [Policy_Number] & "'")


Warm Regards,
Crystal

*
have an awesome day
*

 

[Ted]

fantastic Crystal! thank you soooooo much!
using that piece of code as the criteria did the trick!

thanks again!

Hi Ted,

use this as criteria in the [MaxOfPremium Amount] field

--> dMax("[MaxOfPremium Amount]", "[TableOrQueryName]", "Policy_Number='"
& [Policy_Number] & "'")


Warm Regards,
Crystal

*
have an awesome day
*

Hi all,

I know i've done this before but for some reason i'm having problems
here.

if i have 2 records:

Policy_Number Rating Identification MaxOfPremium Amount
RSA000128-07-00 1 00000058
RSA000128-07-00 3 00001251


how can i pull out the record w the highest premium? If i run a group by
query it groups by Rating Identification so it won't give me the record i
want. i want to pull the record w 3 as the Rating Identification. any
ideas
would be very appreciated. its driving my crazy.

TIA
Ted

 

[strive4peace]

you're welcome, Ted happy to help

Warm Regards,
Crystal

*
have an awesome day
*

fantastic Crystal! thank you soooooo much!
using that piece of code as the criteria did the trick!

thanks again!

Hi Ted,

use this as criteria in the [MaxOfPremium Amount] field

--> dMax("[MaxOfPremium Amount]", "[TableOrQueryName]", "Policy_Number='"
& [Policy_Number] & "'")


Warm Regards,
Crystal

*
have an awesome day
*

Hi all,

I know i've done this before but for some reason i'm having problems
here.

if i have 2 records:

Policy_Number Rating Identification MaxOfPremium Amount
RSA000128-07-00 1 00000058
RSA000128-07-00 3 00001251


how can i pull out the record w the highest premium? If i run a group by
query it groups by Rating Identification so it won't give me the record i
want. i want to pull the record w 3 as the Rating Identification. any
ideas
would be very appreciated. its driving my crazy.

TIA
Ted