retutning top 2 rows for multiple blocks of info

[19FORBES]

I HAVE A TABLE THAT CONTAINS MULTIPLE ROWS OF INFORMATION IN BLOCKS OF TEN ROWS BASED ON A KEY FIELD "CUSIP"

EX.

CUSIP RATE DATE

123 .50 8/1/2002
123 .55 8/2/2002
123 .56 8/3/2002
124 .55 8/1/2002
124 .77 8/2/2002
124 .66 8/3/2002
125 .22 8/1/2002
125 .44 8/2/2002
125 .66 8/3/2002

I AM TRYING TO HAVE A QUERY GO THROUGH AND RETURN THE TOP 2 ROWS OF INFO based on the most recent date (CUSIP,RATE,DATE) FOR EACH SPECIFIC CUSIP (123,124,125.ETC)

I TRIED USING :

SELECT TOP 2 CUSIP,RATE,DATE
FROM TBLHOLDINGS

THIS ONLY RETURNS THE TOP 2 FROM THE FIRST BLOCK OF CUSIPS
(123) IS IT POSSIBLE TO LOOP THRU THE OTHERS BLOCKS OF CUSIPS (124,125,ETC..) AND RETURN THEIR TOP 2 AS WELL????

 

[raskew]

The idea behind this was provided long ago by James Rombough,
back in the days of ElementKJournals forums.
It seems complex, but it works well and I've used it numerous times
to resolve similar situations.

1) Rename field [Date] to a non-reserved term, e.g. myDate
2) Create a totals query, sorting in the desired order:

SELECT tblHoldings.CusIP, tblHoldings.Rate, tblHoldings.MyDate
FROM tblHoldings
GROUP BY tblHoldings.CusIP, tblHoldings.Rate, tblHoldings.MyDate
ORDER BY tblHoldings.CusIP, tblHoldings.MyDate DESC;

3) Turn the above Select query into a MakeTable query and run it

SELECT tblHoldings.CusIP, tblHoldings.Rate, tblHoldings.MyDate INTO xtblTemp
FROM tblHoldings
GROUP BY tblHoldings.CusIP, tblHoldings.Rate, tblHoldings.MyDate
ORDER BY tblHoldings.CusIP, tblHoldings.MyDate DESC;

4) Open xtblTemp in design view and add field [Counter] as autonumber.
5) Create a select query (qryxTemp) on xtblTemp; group by CusIP, and take Min([counter])

SELECT xtblTemp.CusIP, Min(xtblTemp.counter) AS MinOfcounter
FROM xtblTemp
GROUP BY xtblTemp.CusIP;

Create another query based on qryxTemp and xtblTemp, joining on CuspIP. Output
records with xtblTemp.counter < MinOfCounter + 2.

SELECT xtblTemp.CusIP, xtblTemp.Rate, xtblTemp.MyDate
FROM xtblTemp INNER JOIN qryxTemp ON xtblTemp.CusIP = qryxTemp.CusIP
WHERE (((xtblTemp.counter)<[MinOfCounter]+2));