Query to find the second largest year

[hawkins]

I have a table called WPM which containts quite a few columns. The important columns are Years and Tested. I want to create a query that will search for the record that holds the second largest year and in the Years column and display both the year and the tested value. So in short, I need a query to search for the record that holds in second largest year in the table WPM. Ive been able to select the ones that are not the greatest year but it still shows the 3rd and 4th values. Name4ID is used for relationship reasons and the Static.FdrName is used to only allow those selected for the specific name. The code I used is:
SELECT Max(WPM.Years) AS MaxOfYears, Static.FdrName, WPM.Tested, WPM.Name4ID
FROM Static INNER JOIN WPM ON Static.NameID = WPM.Name4ID
WHERE (((WPM.[Years])<(select max(Years) from WPM)))
GROUP BY Static.FdrName, WPM.Tested, WPM.Name4ID
HAVING (((Static.FdrName)=[Reports]![MainRep]![FdrName]));

Any suggestions are greatly appreciated.

[TheShabz]

give this a go. What I did was look for the smallest year from the subquery that pulls the two highest years.

SELECT Min(WPM.Years) AS MinOfYears, Static.FdrName, WPM.Tested, WPM.Name4ID
FROM Static INNER JOIN WPM ON Static.NameID = WPM.Name4ID
WHERE WPM.Years In (SELECT DISTINCT TOP 2 WPM.Years FROM WPM ORDER BY Years DESC) AND Static.FdrName=[Reports]![MainRep]![FdrName]

On a sidenote, try to avoid using "year" and "years" as table or field names. Access reserves certain words and if you use them as object names, you could run into issues later on down the line.

[ajetrumpet]

one way you can do this is to call a function in vba. so, just off the top of my head, you could write this:

SELECT Static.FdrName, WPM.Tested, WPM.Name4ID
FROM Static INNER JOIN WPM ON Static.NameID = WPM.Name4ID
WHERE [Years] = (((GetSecondMaxYear(tableName, fieldName))))
GROUP BY Static.FdrName, WPM.Tested, WPM.Name4ID
HAVING (((Static.FdrName)=[Reports]![MainRep]![FdrName]));

and the function that is called with the above sql would be:

function GetSecondMaxYear(tableName, fieldName) as date

dim rs as dao.recordset
set rs = currentdb.openrecordset("select distinct [" & _
                                      fieldName & "] FROM " & _
                                      tableName & " ORDER BY [" & _
                                      fieldName & "] DESC")

rs.movelast
rs.movefirst
rs.movenext

GetSecondMaxYear = rs.fields(0)

rs.close

set rs = nothing

end function