More than just the Top 15

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Every week I delete and recreate a table that has the
firms that have been contacted the most to the least YTD.
So I need to tag the top 15 with 1,2,3-15. And then all
the rest after will be 16. How can I do this?
 
Hi,


SELECT a.pk, iif(COUNT(*)<=15, 1+COUNT(*), COUNT(*))
FROM myTable As a INNER JOIN myTable As b
ON a.compare <= b.compare
GROUP BY a.pk


with compare the field used to make the ordering; pk is the primary key. You
can add other field, in the SELECT clause, like:

SELECT a.pk, LAST(a.f1), LAST(a.f2), ... , iif(COUNT(*)<=15, 1+COUNT(*),
COUNT(*))
FROM ...



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top