Count of Records that Make Up 80% of a Sum (1 Viewer)

I have a table which essentially is:

- Category
- Item #
- Sales $

I'm looking for a query that will tell me the count of items that make up 80% of the sales per category (probably would have to use a subquery here). So if my table was:

item #, sales $
1, $10
2, $8
3, $6
4, $5
5, $1

Total sales = $30
80% of total = $24
The number of items needed to get to 80% of total sales = 3

Is there any easy way to do this in a query?

mab9 said:

So if my table was:

item #, sales $
1, $10
2, $8
3, $6
4, $5
5, $1

Total sales = $30
80% of total = $24
The number of items needed to get to 80% of total sales = 3

Click to expand...

This is an interesting question. It is easy enough to get the 80% amount - just do a totals query and multiply your result by .8. Then the question is to figure out how many items you need to hit that 80% mark. I think you will need to clarify your logic a bit more to figure this out though. Here are a few questions to think about: How did you get the answer of 3? Is it the smallest number of items it takes to get to $24? Do you start with the least expensive and work your way up? Do you start with the most expensive and work your way down? Do you have to hit the 80% on the nose or can you be over or under? What would the answer be if the third item on your list was $7 instead of $6?