i am not sure if you can do a custom filter such as the one you wanted but if i were to do that query, i would write a module such as:
public sub CustomQuery()
dim dbspace as database
dim topstate, secstate as string
dim topstatedata, secstatedata as currency
Dim rs As Recordset
Dim rs2 As Recordset
set dbspace = currentdb
on error resume next
'if table is missing, ignore
dbspace.execute "DROP Table qryTopTwo"
on error goto 0
dbspace.execute ("CREATE Table qryTopTwo ([State] TEXT, [Statedata] Currency)")
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT state from MyTable")
Do Until rs.EOF
Set rs2 = CurrentDb.OpenRecordset("SELECT * from MyTable where state = '" & rs!state & "'")
'get top two datatopstate = rs2!state
topstatedata = rs2!statedata
rs2.movenext
secstate = rs2!state
secstatedata = rs2!statedata
rs2.movenext
'loop and see if any record tops the first two
Do Until rs2.EOF
if rs2!statedata > topstatedata then
secstate = topstate
secstatedata = topstatedata
topstate = rs2!state
topstatedata = rs2!statedata
end if
if rs2!statedata > secstatedata then
secstate = rs2!state
secstatedata = rs2!statedata
end if
rs2.MoveNext
Loop
'write top two data into qryTopTwo table
dbspace.execute "INSERT INTO qryTopTwo (State, Statedata) values ('" & topstate & "', " & topstatedata & ")"
dbspace.execute "INSERT INTO qryTopTwo (State, Statedata) values ('" & secstate & "', " & secstatedata & ")"
rs2.Close
rs.MoveNext
Loop
rs.Close
End Sub
the above module will create a table named qryTopTwo with result similar to your requirement.
