More efficient way required to rank and report on Top 5 records – possibly VBA

You could use the function below in a query (study the in-line notes please) to rank users by Total Bytes for each School

This will, of course, pull all records.

Thus, you wil have to use a second query to filter on rank 1 to 5, or write the result to a (temp) table and then have a query to filter on rank 1 to 5 using that table as source - hard to tell which method will be the fastest.

However, as you need a report, you could use the first query directly as source for the report and specify the sorting and filtering in this. That will probably be quite fast.

Please note, the key must be strings. It will be Total Bytes which, I guess, is numeric, so use CStr as shown. The same goes for the group key. If a numeric SchoolId use CStr, while a school name can be used as is.

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Select allOpen in new window

/gustav

Try this ...

1.) Paste the code below in a VBA Module.
2.) Create tblTempData based on the fields in your original Data table.

Option Compare Database

Option Explicit

Function RankInternetUsers()
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rstTempData As DAO.Recordset
    
    DoCmd.RunSQL "DELETE * FROM tblTempData"   'Clear Temp Table
    Set rstTempData = CurrentDb.OpenRecordset("tblTempData", dbOpenDynaset)
    
    'Get a list of Unique School Codes
    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [School Code] FROM Query1 ORDER BY [School Code]", dbOpenDynaset)
    
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        Do Until rst.EOF
            'select the top 5 users based on the current school code and total bytes
            Set rst2 = CurrentDb.OpenRecordset("SELECT TOP 5 [School Code], [School Name], User, TotReq, TotSent, TotRecd, TotBytes FROM Query1 WHERE [School Code] = '" & rst![School Code] & "' ORDER BY TotBytes DESC;", dbOpenDynaset)
            If rst2.RecordCount > 0 Then
                Do Until rst2.EOF
                    rstTempData.AddNew
                    rstTempData![School Code] = rst2![School Code]
                    rstTempData![School Name] = rst2![School Name]
                    rstTempData!User = rst2!User
                    rstTempData![Requests] = rst2!TotReq
                    rstTempData![Bytes Sent] = rst2!TotSent
                    rstTempData![Bytes Received] = rst2!TotRecd
                    rstTempData![Total Bytes] = rst2!TotBytes
                    rstTempData.Update
                rst2.MoveNext
                Loop
            End If
        rst.MoveNext
        Loop
    End If


    'Close Recordsets

    If Not rst Is Nothing Then
        rst.Close
        Set rst = Nothing
    End If
    If Not rst2 Is Nothing Then
        rst2.Close
        Set rst2 = Nothing
    End If
    If Not rstTempData Is Nothing Then
        rstTempData.Close
        Set rstTempData = Nothing
    End If
    
    DoCmd.Hourglass False
    MsgBox "Rank Internet Users Process Complete.", vbOKOnly, ""
End Function

Select allOpen in new window

Base your report on tblTempData and add this code to the OnClose Event of the report to clear the temp table.

DoCmd.RunSQL "DELETE * FROM tblTempData;"

See your attached sample db.

ET
Example-19022013-Fixed.accdb

Hi,

you can solve that with SQL, if you create a temporary table which contains an auto number ID as primary key. Simply use a "SELECT...INTO" query from your "Query1" to create the table, add a PK AutoNumber field named "ID" and then you can query the results using five queries for each rank and a result query to assemble them to one. It executes in less than 1 second with around 6000 records as result.

See the attached file for details.

Cheers,

Christian
Example-20022013.zip

Thank for all your quick responses to my question, I am so impressed with the skills here!

I have tried all these solutions and would like to move forward with the solution from etsherman as I found it works the best with the dataset I'm dealing with.

etsherman...
If I can ask for further assistance please. In my original Data table I omitted a field which indicates the user type, these are Students, Teachers, Administrative. To complicate this task further, as well as needing to report on Top 5 users for each school, I'm also needing to add a layer of drilling and report on Top 5 users for each school within each user type. Can this be done within this module? I've added the type field into the Data table as well as Query1 and attached once again.

Also, would it be possible to have a field added to the tblTempData table within the code which numbers the records from highest user to lowest user - highest being 1 and lowest being 5. The numbering needs to be 1-5 for each user, of each type and within each school.

I hope I've explained this well enough.

Thanks again
darls15
Example-20022013.accdb

Thanks so much etsherman! This is exactly the output i need to create my reports from. Your work is very impressive and this solution has completely overcome the issue I was having with the query being slow.

Thanks darls15 ... I build a lot of complex reports for various clients and have run into the same problem you experienced when using sub-queries inside of SQL statements.   I like SQL for most uses but sometimes no matter how well you construct the SQL with a sub-sql statement they just seems to be very inefficient.  Some experts will disagree but I only go by performance.  The function I provided runs in a couple of seconds where as the SQL query can execute for a couple of minutes.  That makes a difference to some clients.

The good part about it is there's more than one way to do it, just have to select what works best for your environment.  Access combined with VBA is a very powerful tool.

If you ever need some custom reports or complex projects outside of doing it yourself with the help found here on EE, just email me and I can assist you.   You can find my email in my profile here on EE and good rates to.

ET

Hi etsherman,

not to say that I showed in the above demo that you can do the job without recordsets in less than a second...;-)

Please keep in mind that working with a database always means: First SQL, then SQL, then long time nothing, then SQL and then, maybe, recordsets.

And to the author: If you have big amounts of data to analyze you should think about not to use Access as backend database. There are lots of free real database servers like SQL Server Express, MySQL, PostgreSQL and so on - they all perform VERY much better than Access SQL, they all have a VERY much better SQL and they all can create Stored Procedures for more complex jobs when a simple SELECT cannot do the job. Todays there is no real reason for using Access as a backend database in an enterprise environment.

Creating a SELECT with SQL Server where you want to get the TOP 5 of a group can be done with one simple SELECT because you can partition the data in one SELECT.

Moreover you should think about your table model and use less character based searches like username or 4 digit school code, use lookup tables with numeric IDs and link them to the main table, use Indexes whereever it can speed up the queries. Use combined indexes if you search for more than one field and so on. These are the real speed advantages of any database - not create VBA code with recordsets. In 99,9999% of all cases it is not necessary and can (and should) be done with SQL. In SQL it doesn't matter if the code looks ugly, the main thing is that it performs fast. That's often hard to understand for programmers because it often "doesn't look good, nice".

Cheers,

Christian

Hi Christian

Thank you for your comments and suggestions. I totally agree and with you in regards to better systems to handle this type of job, however Access is the "chosen" system from which I have to work with.

The SQL query method you provided worked very well and I believe it will be very useful in other areas of my work. I am very thankful for your post because, as etsherman said, it's provided me with more than one way of accomplishing what I have to do.

I will definately take on board your suggestions regarding my table models and indexing. I did try indexing the main fields in the first instance but it had not much effect on the speed of my query, so not sure if I had done it right or not. I will now go off and do a bit of research into indexing to lift my knowledgebase. I'm an average user when it comes to working with Access but also very keen to learn and advice such as this helps me a lot.

Thanks again
darls15

Hi Christian ...

As commented earlier ... My comments are based on personal experiences over the many years of developing Access reports no matter what the back-end server database.  I generally use SQL first then VBA code.  My experience has been SQL with a sub-query for the most part runs slower than a VBA function.  

ET

Hi ethserman,

I hope you are not angry if I say: If the table model and indexes are made correctly I guarantee that SQL will ALWAYS outperform any VBA method. And "no matter which backend database" is simply wrong. Any database server is faster than Access, simply because you have more powerful SQL commands and an active backend. For example, compare the syntax diagram of UPDATE with any database server or compare a CTE of SQL Server with subqueries of Access...
I would agree if you say, there are special situations where VBA can be of help or where you are forced to use VBA because SQL cannot give a better solution, but only if you have Access as backend. Whenever you use a backend which is able to create Stored Procedures there is simply NO situation where VBA can do something which SQL cannot do better (where we are speaking of queries, updates, deletes, inserts and not special requests like distribute some table data to some fields of a form or something like this). In Access you are sometimes forced to use VBA because Access SQL is so primitive, but even with Access SQL (which is not slow if it is used correctly) you can often exchange a VBA procedure by SQL if you think about it a little bit.
Access has some intelligence when using VBA functions inside of SQL to speed up the results, where you would expect that a VBA function runs on every row of a query it is executed only once and the result is cached by Access. So for example using a DCount instead of a SELECT Count subquery can often be surprisingly faster. If Access SQL would depend on the speed of VBA nobody would use it inside SQL...;-)

I would bet that I can outperform any of your VBA procedures in any database using SQL for reporting, at least the very most, if the table structure is OK.

Hi darls15,

I would say: Maybe it should be part of your job to convince the people who "chose" this kind of backend to use a better solution - because the alternatives are all more comfortable, more secure and completely free of costs.

But of course you can slow down any database if you have no strict table model which is indexed as good as possible. If that's not the case before it's time to do it as soon as possible even if that means to rewrite a lot of code. In an enterprise environment I would say that's an absolute requirement because the costs it produces to work with unprofessional databases are very much higher than to pay a little bit more at the beginning to create a clean, stable table model.

But of course I didn't want to start a discussion about all that with you both, I only wanted to write my opinion about that.

Good luck for you both with your projects

Christian

>>>>>I hope you are not angry if I say: <<<<<<

Not at all ... I think what you are missing in your comments is my original post relating to using VBA code vs SQL is focused on a Access Query object that contains a Sub-Query which is exactly what darls15 was working with in his original sample.  I have run into cases where a VBA Function will out perform a Query with a Sub-Query.  That's been my experience and I will stand by it.

Take care ...

ET