Sort Cust list into 80/15/5 Groups

[ScottR3]

Currently I export a customer list into Excel along with their YTD or
trailing twelve month sales totals. The sales are summed to find the
total sales of the group and then muliplied by 80%, 15% and 5%. The
customer list is then sorted in descending dollar amounts so that I
can easily divide the list into three groups. "A" customers bring in
80% of the sales. "B" customers bring in the middle 15% and "C"
customers the final 5%.

This is really easy to do in Excel but a completely manual process.
I'm hoping to make the rankings "live" buy creating some Access
queries.

The customer list was easy enough. I was already using an Access query
to generate the Excel file.

Summing the Sales and calculating 80%, 15% and 5% was easy too.

I'm stumped at the last step. Assuming the customer list is sorted in
descending dollar value, how do I break out the list to find the group
of customers to be assigned an "A" and a second group assigned to
"B"?

Whatever is left over will be assigned to "C".

Thanks;

Scott

 

[Golfinray]

You could make 3 simple queries. Go into sql view and type:
select top 5 percent *
from [your table or query]

Your next would be :
Select top 15 percent *
from ............. etc

 

[Michel Walsh]

SELECT a.Customer, SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.sales <b.sales
GROUP BY a.Customer
ORDER BY SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)



should list the cumulative % of sales (starting with the 'biggest' client).


Alternatively, you can use:


SELECT a.Customer,
SWITCH( SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere) <=0.80, "A",
SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)
<=0.95, "B",
true, "C" ) AS class
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.sales <b.sales
GROUP BY a.Customer
ORDER BY SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)



should supply the class (A, B, or C) directly, but note that the percentage
are then hard coded in the SQL statement.



Hoping it may help,
Vanderghast, Access MVP

 

[raskew via AccessMonster.com]

Very nice solution!

Bob

SELECT a.Customer, SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.sales <b.sales
GROUP BY a.Customer
ORDER BY SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)

should list the cumulative % of sales (starting with the 'biggest' client).

Alternatively, you can use:

SELECT a.Customer,
SWITCH( SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere) <=0.80, "A",
SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)
<=0.95, "B",
true, "C" ) AS class
FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.sales <b.sales
GROUP BY a.Customer
ORDER BY SUM(b.sales)/(SELECT SUM(sales) FROM tableNameHere)

should supply the class (A, B, or C) directly, but note that the percentage
are then hard coded in the SQL statement.

Hoping it may help,
Vanderghast, Access MVP

Currently I export a customer list into Excel along with their YTD or
trailing twelve month sales totals. The sales are summed to find the

[quoted text clipped - 23 lines]

Scott

 

[ScottR3]

Michel;

Thanks for the very nice solution. I can tell it's nicebecause Bob
said so. I'm a bit handicapped because I've always used the Design
View rather than the SQL View. Could you add a little bit of a plain
english description to what's going on in your two examples?

Thanks,

Scott

 

[Michel Walsh]

(SELECT SUM(sales) FROM tableNameHere)


computes the total sales, for all customer.


SUM(b.sales)
also given that we said:

ON a.sales < b.sales
GROUP BY a.customer


is the total of all sales greater than the one of the customer in
consideration (the customer making the 'group' can be labeled as the
'customer into consideration', since he defines the group, so somehow, who
makes it to be included in the sum, and who (which record) does not make
it). You can use:


ON a.sales <= b.sales


if you want to include its own sales into the sum.


So,

SUM(b.sales) / (SELECT SUM(sales) FROM tableName)

will be the % of all sales for the customers having a bigger sales than
yours (option: including your sales, if you use <= instead of < in the ON
clause).


The SWITCH uses that values to know if it is class A (the % is below 80%,
or 0.80 if you prefer), class B (the % is below 95%) or class C (for all
others).




Hoping it may help,
Vanderghast, Access MVP


Michel;

Thanks for the very nice solution. I can tell it's nicebecause Bob
said so. I'm a bit handicapped because I've always used the Design
View rather than the SQL View. Could you add a little bit of a plain
english description to what's going on in your two examples?

Thanks,

Scott