max Function

[chrislabs]

I know that the Max functions get the highest value but is there a way to get the second or thrid highest value??

 

[llkhoutx]

Run successive queries saveing the maximun. In each successive query filter out the previous found maximum.

Alternatively -

Cycle through your records one by one using DAO/ADO and save the highest three values.

 

[Dennisk]

Even Easier use a query and sort descending and specify TOP 3

ie

SELECT TOP 3 [MH Patient].MHID
FROM [MH Patient]
ORDER BY [MH Patient].MHID DESC

 

[llkhoutx]

Good post Dennisk.

 

[Oksana]

"Second" maximum

To find second highest value:

SELECT Max([Number]) AS SecondMax
FROM SomeTable
WHERE [Number]<DMax("Number","SomeTable");