Second Highest Number

[mab9]

I'm trying to identify the 2nd highest number in a field in Access. While searching, I ran across the =large(a1:a3,2) formula, which works perfectly in Excel. I'm trying to duplicate this functionality in Access 2003, but it looks like this formula isn't in there. Any ideas on how to accomplish this?

 

[pbaldy]

One way would be 2 (or nested) queries; the first finds the top 2 values and the second gets the smaller of those 2. If I recall correctly, the MS Sample Queries db contains a sample of this.

 

[CraigDolphin]

Another way would be one query (finding the top two values as above) and using DMin on the field containing the values in the query.

 

[Cosmos75]

Assuming we have a table called tblData with a number field (LONG) called lngValue, here are two ways to go about it.

Using DMax()

SELECT DISTINCT DMax("[lngValue]","tblData","[lngValue]<(SELECT Max(lngValue) FROM tbldata)") AS SecondHighest
FROM tblData AS a;

Using SELECT DISTINCT, TOP, and SELECT statement criteria

SELECT DISTINCT TOP 1 tblData.lngValue AS SecondHighest
FROM tblData
WHERE (tblData.lngValue<(SELECT DISTINCT TOP 1 Max(a.lngValue) from tblData as a))
ORDER BY tblData.lngValue DESC;


I'm sure there are other and better ways but here's what I came up with a few mins work.

 

[mab9]

I guess the be a little more specific, I'm building this into a form. On the detail section, this is going into the conditional formatting in order to highlight that entire row when the first or 2nd high numbers come up. For the max number, I'm using:

Expression is: [gam potential]=Max([GAM Potential])

 

[Cosmos75]

mab9,

How about setting the conditional formatting to
Field Value equal to DMax("[lngValue]","tblData","[lngValue]<(SELECT Max(lngValue) FROM tbldata)")