I have a table (call it
myTable) in MS Access with two fields:
Item (Text) and
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
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.
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.
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("SELECT Sum(Quantity) FROM myTable") With rstRs1 If Not .EOF Then .MoveFirst lngTot = .Fields(0) * dblPct End If .Close End With Set rstRs1 = Nothing If 0 < lngTot Then Set rstRs2 = dbsCdb.OpenRecordset("SELECT * FROM myTable ORDER BY Quantity DESC") With rstRs2 If Not .EOF Then .MoveFirst Do Until .EOF Or lngSum >= lngTot lngRcd = lngRcd + 1 lngSum = lngSum + !Quantity .MoveNext Loop End If .Close End With Set rstRs2 = Nothing DoCmd.RunSQL "SELECT TOP " & lngRcd & " * INTO OutputTable FROM myTable ORDER BY Quantity DESC" 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
Quantityof all records in
myTableand multiply this by the given percentage argument (e.g. 0.8 for 80%).
- I then iterate over the records in descending order of
Quantityand 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
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!
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!