Query to do Top 10 and everything else

[Jon]

I know how to create a query that can pull out the Top 10 records in a
field. What I would like to do is create a top 10 query, then have that
same query bunch all the other records into an eleventh line.

Let me simplify it using an analogy. Say I have 5 records:

Able 3
Baker 5
Charlie 2
Delta 8
Eagle 4

If I run a top 2 report on this, it will come back saying:

Delta 8
Baker 5

What I want is to ultimately create a report that will come back saying
this:

Delta 8
Baker 5
Other 9

How do I do it??

 

[Tom Ellison]

Dear Jon:

I suggest you use a UNION query to do this.

You need one query that gives the TOP 10.

Create another that get the sum of the numeric value for the rest of the
rows.

Something like:

SELECT TOP 10 Doofus, Ct
FROM SomeTable
ORDER BY Ct DESC
UNION ALL
SELECT "Other",
SUM(Ct)
FROM SomeTable
WHERE Doofus NOT IN (
SELECT TOP 10 Doofus
FROM SomeTable
ORDER BY Ct DESC)

You will need to fix up the table and column names to what you actually
have.

Tom Ellison

 

[Jon]

Thanks. I'll give it a shot.

Dear Jon:

I suggest you use a UNION query to do this.

You need one query that gives the TOP 10.

Create another that get the sum of the numeric value for the rest of the
rows.

Something like:

SELECT TOP 10 Doofus, Ct
FROM SomeTable
ORDER BY Ct DESC
UNION ALL
SELECT "Other",
SUM(Ct)
FROM SomeTable
WHERE Doofus NOT IN (
SELECT TOP 10 Doofus
FROM SomeTable
ORDER BY Ct DESC)

You will need to fix up the table and column names to what you actually
have.

Tom Ellison