Return Bottom Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that I am trying to use for a graph. Each customer in the
database has a primary product, and the query simply counts how many
customers have their primary product as each product. E.g.:

Hammer 12
Axe 9
Rake 7
etc....

What I'd like to do when the chart is created, is have it only use the top 4
products, and then lump the rest into an "Other" category. I didn't find a
way to do that with the chart tool in Access, so I was thinking that I could
have the query return just the top 4, and then have another query which
captured everything else, and then lumped them together. So I guess my
question is, is there any way to have a query return all BUT the top 4?
(Note, the number of products will vary, so inverting the order of the query
and then returning, for example, the top 8 of 12 wouldn't work, because there
wouldn't always be 12.) Thanks in advance for any insights.
 
I know this is not pretty. I don't have a good data source to work with so I
made something up. Let me know if you need more explanation:

SELECT TOP 4 Table1.Name, Table1.Value
FROM Table1
ORDER BY Table1.Value DESC
union SELECT all "All Others" AS Name, Sum(Table1.Value) AS SumOfValue
FROM Table1
WHERE (((Table1.Name) Not In (SELECT TOP 4 Table1.Name
FROM Table1
ORDER BY Table1.Value DESC)))
GROUP BY "All Others";
 
Back
Top