Select Records Constituting Percentage of Total The Setup
I have a table (call it myTable) in MS Access with two fields: Item (Text) and Quantity (Long).
For example, the first 10 records might be:
Item      |  Quantity
----------+----------
Item001   |       706
Item002   |       533
Item003   |       580
Item004   |       289
Item005   |       302
Item006   |       775
Item007   |        14
Item008   |       761
Item009   |       815
Item010   |       709


The Task
I'm looking to obtain a table/query containing the top N records whose Quantity constitutes a given percentage of the total Quantity across all records.
For example:
If the total of the Quantity field for all records is 1000 and the given percentage is 80%, the output table/query would contain sufficient records in decreasing Quantity until the total Quantity is 800.

My Code
Here is my current approach:
Function Percentile(dblPct As Double)
    On Error GoTo Cleanup
    Dim dbsCdb As DAO.Database
    Dim rstRs1 As DAO.Recordset
    Dim rstRs2 As DAO.Recordset
    Dim lngTot As Long
    Dim lngSum As Long
    Dim lngRcd As Long
    
    Set dbsCdb = CurrentDb
    Set rstRs1 = dbsCdb.OpenRecordset(&quot;SELECT Sum(Quantity) FROM myTable&quot;)
    
    With rstRs1
        If Not .EOF Then
            .MoveFirst
            lngTot = .Fields(0) * dblPct
        End If
        .Close
    End With
    Set rstRs1 = Nothing
    
    If 0 &lt; lngTot Then
        Set rstRs2 = dbsCdb.OpenRecordset(&quot;SELECT * FROM myTable ORDER BY Quantity DESC&quot;)
        With rstRs2
            If Not .EOF Then
                .MoveFirst
                Do Until .EOF Or lngSum &gt;= lngTot
                    lngRcd = lngRcd + 1
                    lngSum = lngSum + !Quantity
                    .MoveNext
                Loop
            End If
            .Close
        End With
        Set rstRs2 = Nothing
        
        DoCmd.RunSQL &quot;SELECT TOP &quot; &amp; lngRcd &amp; &quot; * INTO OutputTable FROM myTable ORDER BY Quantity DESC&quot;
    End If
       
Cleanup:
    On Error Resume Next
    If Not (rstRs1 Is Nothing) Then rstRs1.Close: Set rstRs1 = Nothing
    If Not (rstRs2 Is Nothing) Then rstRs2.Close: Set rstRs2 = Nothing
    If Not (dbsCdb Is Nothing) Then Set dbsCdb = Nothing
End Function


I first calculate the total Quantity of all records in myTable and multiply this by the given percentage argument (e.g. 0.8 for 80%).
I then iterate over the records in descending order of Quantity and calculate a running total, ceasing when this total is greater than or equal to the target, counting the number of records it takes to do so.
Finally, I select the calculated number of records from the table to create an OutputTable.


What I'm Hoping For

I'm relatively new to VBA, so feedback on any obvious bad practices in my code would be appreciated.

I feel like I'm missing something obvious - could this be achieved using one or two SQL statements?



Many thanks in advance for your time!
This is very good question and a very hard one because your query is basically sensitive to the ordering. In other SQL database engines, this is easier to solve with use of window functions. Unfortunately, Access database engine does not support window functions.

A possible choice is to use subquery but this can get very expensive quickly because a subquery would have to be correlated and therefore re-calculated for all possible rows, making it O(too much n). For completeness' sake, here's a possible query:

SELECT 
  m.Item, 
  m.Quantity, 
  (
    SELECT SUM(x.Quantity) 
    FROM myTable AS x 
    WHERE x.Quantity &gt;= m.Quantity
  ) AS RunningSum, 
  (
    SELECT SUM(t.Quantity) 
    FROM myTable AS t
  ) * 0.8 AS PercentileThreshold
FROM myTable AS m
WHERE (
  SELECT SUM(x.Quantity) 
  FROM myTable AS x 
  WHERE x.Quantity &gt;= m.Quantity
) &lt;= (
  SELECT SUM(t.Quantity) 
  FROM myTable AS t
) * 0.8
ORDER BY m.Quantity DESC;


To avoid paying the expenses of multiple queries being executed several times over, you can combine some VBA with SQL. For instance, you can create the following VBA in a public standard module:

Private Type TPercentile
    PercentileThreshold As Currency
    PercentileValues As Scripting.Dictionary
End Type
Private This As TPercentile

Public Function StartPercentile(PercentileThreshold As Currency) As Boolean
    This.PercentileThreshold = Nz(DSum("Quantity", "myTable") * PercentileThreshold, 0)
    Set This.PercentileValues = New Scripting.Dictionary
    StartPercentile = True
End Function

Public Function Percentile(Key As String, Quantity As Variant) As Currency
    Dim PercentileValue As Currency

    If Not This.PercentileValues.Exists(Key) Then
        PercentileValue = Nz(DSum("Quantity", "myTable", "Quantity&gt;=" &amp; Nz(Quantity, 0)))
        This.PercentileValues.Add Key, PercentileValue
    End If
    Percentile = This.PercentileValues(Key)
End Function

Public Function PercentileThreshold() As Currency
    PercentileThreshold = This.PercentileThreshold
End Function


Your query then becomes:

SELECT 
  m.Item, 
  m.Quantity, 
  Percentile(m.Item,m.Quantity) AS Percentile, 
  PercentileThreshold() AS Threshold
FROM myTable AS m
WHERE StartPercentile(0.8) 
  AND Percentile([m].[Item],[m].[Quantity]) &lt;= PercentileThreshold()
ORDER BY m.Quantity DESC;


This enables you to handle a larger dataset by populating the dictionary only once and require less code to boot and you can easily generalize for other tables. The downside is that this is limited to only one active running query at a time. If this is unacceptable, then you need to add some kind of identifier that each query can then generate in their invocation to StartPercentile() function so that they have their own "instances". 

I also want to call your attention to one possible option for you - If you are reporting, Access already provides you with a Running Sum property on the Report so you could conceivably create a simple query, then apply a filter on a calculated control, rather than doing in the query. That yields much less code. 

You may wonder why I use Currency all over the place -- the answer is that I generally avoid using floating numbers such as Double which can cause unstable results due to rounding errors. Currency is a scaled integer and thus is not subject to such problems so it is a great data type to use for where you need fractions less than 4 decimal places. I should note that there is also a Decimal type but IME, it yields more problems than solutions and I never use it within Access/VBA context.