Top Values

[ISUTri]

I am trying to find the top 50 values per month from 3 years of data. Each month has over a 1000 records. Other than doing this manually how can I get Access to return the top 50 of each month?

Here is the SQL for one query I ran. Which is just grabbing the Info for one particular month.

SELECT TOP 50 tblFillRateSummary3YEARS.RptMth, tblFillRateSummary3YEARS.PartNbr, tblFillRateSummary3YEARS.TotalLinesOrdered
FROM tblFillRateSummary3YEARS
WHERE (((tblFillRateSummary3YEARS.RptMth)=#8/1/2002#))
ORDER BY tblFillRateSummary3YEARS.TotalLinesOrdered DESC;

 

[PHV]

Something like this ?
SELECT RptMth, PartNbr, TotalLinesOrdered
FROM tblFillRateSummary3YEARS AS A
WHERE TotalLinesOrdered In (SELECT TOP 50 TotalLinesOrdered
FROM tblFillRateSummary3YEARS WHERE RptMth=A.RptMth ORDER BY 1 DESC)
ORDER BY 1, 2 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886