Cumulative percent: show me the items that contribute to the first 10% of the sales

This is a common business problem, and not one that's easily solved with simple queries or reports. Look around the web and you'll find a number of solutions. Many of those solutions only work in reports and require VBA coding. Here's one that uses only a series of queries and will not require you to get into the muck of SQL or VBA.

Here's sample data for this example:

The question at hand is: how much space is allocated to items that contribute the last 25% of the margin dollars.

Let's show how this would be done in Excel. It may illustrate the concept better:

Having sorted the data and figured out cumulative margin and the percent of the total that represents, we know that in category 2, two items contribute the first 69% of the margin dollars, and the remaining three only contribute 31%. In Excel you have to build formulas by category and so it becomes tedious for large amounts of data. As well, the rest of the analysis you're doing may be in Access so to figure out the cumulative % in Excel would mean moving data back and forth- cumbersome and error-prone!

To do this in Access we need queries that will do the same thing we did using formulas in Excel.

The first query is the hardest. We need to sort the items in descending order and figure out cumulative sales at each item. Here's what it looks like in the query design grid:

The first thing you notice is that tblPerformance is in the query twice. Just add it twice in the Show Table dialog, and Access will automatically give it another name: tblPerformance_1 so that it (and you) can keep the two copies separate.

The two copies of the table are first joined on CategoryNumber, so that the sorting and summing is done within each category. The last column is where the magic happens. Access looks at the two copies of the table, and for each item finds all the items with margin greater than  or equal to that item. Then the sales for those items are summed. This is similar to the Excel example where we sum all the items in the list above the item we're calculating for. Because tblPerformance has two different names we can tell it which table to sum the values from and how to compare the value in one table to the other.

The rest of the queries are easy. You need one that finds the total margin by category:

And one that uses those two queries to turn cumulative dollars into cumulative percent:

Now you can use that query as a filter to find values to sum to answer the original question (how much space is allocated to items that contribute the last 25% of the margin dollars)

Performance:
A query like this can be quite resource intensive. If you run it on a large table you may find it takes too long to run. Consider making the first two queries make table queries and building the third query off the tables. Or try putting indexes on your data table. For this example I built indexes like this: