Selecting records by a calculation

[chris-evans]

Hoping someone can help me.

I have a table of a couple of thousand records, it lists customer name in one field and revenue in the next. I want to run a query where it give me all the customer names that comprise 80% of the total revenue for the table.

Is this possible and if so how can I do this!?

Many thanks

Chris

 

[Andrew Fergus]

Hi Chris

I got this to work using 3 separate queries, as follows:

Query 1

SELECT tblSales.Customer, Sum(tblSales.Sales) AS SumOfSales, (SELECT Sum([Sales]) FROM tblSALES) AS GTOTAL
FROM tblSales
GROUP BY tblSales.Customer
ORDER BY Sum(tblSales.Sales) DESC;

This is the SQL for the first query (saved as qrySales1) - you can get into the SQL view by selecting View > SQL View when in the query design screen. This query adds up the total sales by customer and sorts them in descending order and also provides a grand total for sales in the table.

Query 2

SELECT qryAlias.Customer, qryAlias.SumOfSales, qryAlias.GTOTAL, (SELECT Sum([SumOfSales]) FROM qrySales1 WHERE SumofSales >= qryAlias.SumofSales) AS RunTOTAL, [RunTOTAL]/[GTOTAL] AS MyPercent
FROM qrySales1 AS qryAlias;

This query calculates a running total and a percentage running total. Save this query as qrySales2.

Query 3

SELECT qrySales2.Customer, qrySales2.SumOfSales, qrySales2.MyPercent
FROM qrySales2
WHERE (((qrySales2.MyPercent)<=(SELECT Min(MyPercent) FROM qrySales2 WHERE qrySales2.MyPercent >= 0.8)));

This query only displays the largest customers who provided at least 80% of the total sales value. I seemed to get an error when I tried to sort this last query (Giacomo are you watching? Any clues on that one?) so beware of that.

Use your actual actual table name in place of 'tblSales' are your actual field names in place of 'Customer' and 'Sales'.

HTH, Andrew

 

[giacomo]

(Giacomo are you watching? Any clues on that one?)

Andrew, instead of sorting the field by name try using order by 3 DESC and see if that works.

Giacomo

 

[chris-evans]

That's great, thanks!