Finding top ten records for each Name

[maic15]

I'm trying to find the top ten records for data that has more than 10 records for each name.. For example,

PCP DOS
John
1/5/2006
1/6/2006
1/7/2006
1/8/2006
1/9/2006
1/10/2006
1/11/2006
1/12/2006
1/13/2006
1/14/2006

Tom
2/12/2004
2/13/2004
2/14/2004
2/15/2004
2/16/2004
2/17/2004
2/18/2004
2/19/2004

Ideas?

 

[Andrew Fergus]

Hi, you can do this with a select query provided your table has both the name and date in each row.

Create a new query, add the relevant table, view totals, add the name (sort ascending) and date (sort descending) fields to your query, right click the table -> properties -> change the table alias to a new name (e.g. "tblAlias" without the quotes), add a third field to your query, as follows :

Rank: (Select Count(*) from tblMaic Where ([PCP]=tblAlias.[PCP]) and ([DOS]> tblAlias.[DOS]))+1

Change "Group By" to "Expression" and add the following to the criteria of the rank field : "<=10" (without the quotes).

Please note that I used "tblAlias" as the Table Alias for the table I called "tblMaic", and I used the field names "PCP" and "DOS" (per your example). Make sure you change the table and alias and field(?) names to reflect the actual names you are using.

The SQL for the query follows :

SELECT tblAlias.PCP, tblAlias.DOS, (Select Count(*) from tblMaic Where ([PCP]=tblAlias.[PCP]) and ([DOS]> tblAlias.[DOS]))+1 AS Rank
FROM tblMaic AS tblAlias
GROUP BY tblAlias.PCP, tblAlias.DOS
HAVING ((((Select Count(*) from tblMaic Where ([PCP]=tblAlias.[PCP]) and ([DOS]> tblAlias.[DOS]))+1)<=10))
ORDER BY tblAlias.PCP, tblAlias.DOS DESC;

HTH, Andrew.