Selecting the highest value from multiple records without the group cl

[ciarra41]

I’m trying to select the highest value where there are multiple records in other fields. All I need to see is highest payment from the PAYMENT field or flag it by High or Low.


IIf(IsNull([PAYMENT]),"",IIf(DMax("PAYMENT","tblcostamount","[PAYMENT] = '" & [PAYMENT] & "'")>1,"High","Low"))

Example:

LOTNUM LOCAL PAYMENT
5241246621 211D5 47
5241246621 211D5 111
3251247894 362W2 56
3251247894 362W2 159
3621245512 55S221 131
3621245512 55S221 37

So, from the example I would flag or see row 2,4 and 5.

 

[lespaul]

You don't mention it in the post, but from part of your title I'm guessing you don't want to use a GROUP BY clause....why not? That's the easiest way to get the information you want:

SELECT LotNum, Local, Max(Payment) FROM TableName GROUP BY LotNum, Local

Additionally, If you're always getting the highest payment, what would you flag as "low"?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ciarra41]

Ok, I manage to figure out what I did wrong. But know I need to revise this SQL to show the null value as the (min) value.

Mymaxvl: Min(Nz(IIf([PAYMENT]=0,"",[PAYMENT])))

5241246621 211D5
5241246621 211D5 111

 

[PHV]

Like this ?
Mymaxvl: Min(Nz([PAYMENT],0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886