Totals Query - Return accounts that generate 80% of sales

[SalesOp]

Hello,

I am trying to do a Totals query that would return accounts that produce 80% of the total revenue. I hope to sort the sum of revenues from each account in descending order then select these top accounts whose revenue sum is 80% of the total sales. The idea is to find out which and how many accounts give me 80% of my business. Also, how will I find the number of accounts that contribute to 80% of sales.

Please see the attached DB. The attached query only lists all accounts ordered in descending order.

I appreciate any help.
SalesOps

 

[Minty]

In simple terms, If you can create a query that lists the amount of turnover and sums the overall total, you can then run a query on that as to get your 80%. Your attached file won't work because the data is linked to a excel sheet.

 

[SalesOp]

Sorry the data was linked, Now attached again with actual table.

 

[Minty]

I haven't done this in a complicated way, just to show you the building blocks.
See the attached to give you an idea.

 

[SalesOp]

Hi Minty

Thanks for the help but I am still a bit stuck. Once the sales figures are sorted in descending order with the percentage of sales in right column, how do i get the top accounts whose sales sum up to 80% of the total sales value.

AccountId ProdCat AccTotal PercOfTotal
R10002 Retail 6671.72 20.84%
B10001 B2B 5312 16.59%
B10005 B2B 3532 11.03%
R10007 Retail 2460 7.68%
R10006 Retail 2397 7.49%
B10008 B2B 2358 7.36%
C10010 Consumer 1816 5.67%
C10006 Consumer 1347 4.21%
B10003 B2B 1200 3.75%
C10009 Consumer 1152 3.60%
C10007 Consumer 870 2.72%
R10003 Retail 600 1.87%
C10008 Consumer 600 1.87%
B10009 B2B 576 1.80%
B10004 B2B 409 1.28%
R10008 Retail 300 0.94%
C10003 Consumer 200 0.62%
C10001 Consumer 159 0.50%
R10009 Retail 60 0.19%

 

[jdraw]

Here is one approach based on your database. There are comments in the code. And the output of the routine is a message box with the logic used and the records/accounts that are the top 80 %.

Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : getTop80PCT
' Author    : mellon
' Date      : 22-Oct-2016
' Purpose   : This is one approach to resolve the question
'  at http://www.access-programmers.co.uk/forums/showthread.php?t=290013
'---------------------------------------------------------------------------------------
'
Sub getTop80PCT()
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim tot As Double
          Dim mtext As String
10       On Error GoTo getTop80PCT_Error

20        tot = DSum("acctotal", "qryAcctTotalPerc")

30        mtext = mtext & "100 per cent of Sales is " & tot & vbCrLf
          Dim Pct80 As Double
40        Pct80 = 0.8 * tot
50        mtext = mtext & " 80 % of sales is  " & Pct80 & vbCrLf
60        mtext = mtext & " So process the records and accumulate sales until you exceed the 80% value " & vbCrLf _
                  & vbCrLf & "These records account for 80% of Sales" & vbCrLf & vbCrLf
70        tot = 0    'set tot to 0 to accumulate values of rs!acctotal
80        Set db = CurrentDb
90        Set rs = db.OpenRecordset("qryAcctTotalPerc")
100       Do While Not rs.EOF
110           tot = tot + rs!acctotal    'increment the tot
120           mtext = mtext & rs!AccountID & " " & rs!acctotal & " " & rs!Percoftotal & "  " & tot & vbCrLf

130           If tot > Pct80 Then GoTo Done
140           rs.MoveNext
150       Loop
Done:
160       MsgBox vbCrLf & vbCrLf & mtext

170      On Error GoTo 0
180      Exit Sub

getTop80PCT_Error:

190       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure getTop80PCT of Module Module1"
End Sub

good luck.

 

[SalesOp]

Many thanks jdraw, I will try it out, much appreciated