<

Random Rows in Microsoft Access

Published on
4,738 Points
738 Views
Last Modified:
Editors:
Generating random numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more work. Here we will show how to accomplish this with a few lines of code.

This is the first article on row numbers in Microsoft Access.

The second is about Sequential Rows in Microsoft Access.


What is it for?

Typically, the reason for assigning a random number to each record of a recordset is to be able to sort these in a sequence completely out of any normal order - like ID, customer number, purchase date, etc. As the assigned numbers are random, when sorting on these, any other order will appear to be random.


That's the basics. Next question is why or for what purpose?


That is usually to select a small count of records from a large set of records where you don't want these to be sorted by something - it could be for statistics, sample quality control, or similar.


Random, or just apparently random

It is important to understand, that generating truly random numbers is an art of its own, that still can keep mathematicians busy: Randomness.


Luckily, for our purpose, a less-than-ideal method can be used: pseudo-random numbers. This is what the native function of VBA, Rnd, offers.


What this means is, that even if the generated numbers may appear random, they are not. They are picked from a long pre-generated list of numbers having an even distribution between 0 and 1. This means, that if you sum these and calculate the average, the result will be close to 0.5.


This you can verify quickly. Type into the Immediate Window this line:


s = 0 : For i = 1 to 1000000 : s = s + Rnd() :Next : ? s / i

and press Enter, and you will get results like these:


0.500089374910625
0.499534094215906
0.499724781525218
0.49985175014825
0.500306843443157


All values are, as seen, very close to 0.5.


However, as the values are picked from a sequence, the first pick must start somewhere, and - if no precaution is taken - that will be from the start. The first five values can easily be listed:


For i = 1 to 5 : ? Rnd() : Next

If you open the application and run this code, those five values will always be:


0.7055475
0.533424
0.5795186
0.2895625
0.301948


Having the same values returned defeats any purpose of random. Thus, a command, Randomize, exists that will set the start point to somewhere in the predefined sequence of pseudo-random numbers and that somewhere is controlled by the time of the day. 


This still isn't random, though you would have to open the application and run the command at the exact same split second of the day, which is not likely to happen.


To conclude, to obtain five seemingly random numbers, call the code like this:


Randomize
For i = 1 to 5 : ? Rnd() : Next


Random values in queries

Until now, code has been VBA only, not SQL. And while you can use a function - user-defined or native of VBA - in an Access query, a statement, which is what Randomize is, can not. To overcome this limitation, you can create a user-defined function that calls Randomize.


Or, you can call Rnd() with a parameter having a negative value. Again, this parameter value could be generated by Timer()  to obtain different start points for the value series returned:


Select *, Rnd(-Timer()) As RandomNumber From YourTable

But this has two issues. First and most important, it will return the same number for all records, as the expression will be called once only; second, it will not return values with an even distribution between 0 and 1.

The uneven distribution can easily be demonstrated:


s = 0 : For i = 1 to 1000000 : s = s + Rnd(-Timer()) : Next : ? s / i

This will not result in average values close to 0.5. In fact, they can vary between 0.2 and 0.8!

To solve both issues, include the primary key (typically [ID]) in the expression:


Select *, Rnd(-Timer() * [ID]) As RandomNumber From YourTable

This will force a call of the expression for each record, and it will make the distribution even - which you easily can check out:


s = 0 : For i = 1 to 1000000 : s = s + Rnd(-Timer() * i) : Next : ? s / i

The returned average will now be very close to the desired value of 0.5.


Random sorting of a query

At this point, we should be able to apply some random order to a query. For example, using the Order Details table from the attached demo, holding a few tables from the Northwind 2007 sample database from Microsoft.


SELECT 
    ID, 
    [Order ID], 
    [Unit Price], 
    Rnd(-Timer()*[ID]) AS RandomRecord
FROM 
    [Order Details]
ORDER BY 
    Rnd(-Timer()*[ID]);

Run it, and you'll see the records in some random order as determined by the generated values in the field RandomRecord - all fine and dandy - except for one thing which will be either a major advantage or a major disadvantage:


Whenever the records are required, resorted, or refiltered, all values of field RandomRecord will be regenerated.


If this is desired is, of course, determined by the purpose of the query. 

If it is, you are all set. If not, another method must be used.


Persistent random sorting of a query

If your scenario requires what would seem to be persistent random numbers, an external function must be used in an expression for a field in the query. This will generate - and keep - a pseudo-random number for each record. 


What this means is, that - once generated - the random numbers will resist any change in ordering or filtering of the query, even a requery.


The method to collect the numbers is to use - a collection. For each key, a random number is generated and collected bound to its key. It takes only a few lines of code:


' Builds random row numbers in a select, append, or create query
' with the option of a initial automatic reset.
'
' 2018-09-11. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RandomRowNumber( _
    ByVal Key As String, _
    Optional Reset As Boolean) _
    As Single
    ' Error codes.
    ' This key is already associated with an element of this collection.
    Const KeyIsInUse        As Long = 457
   
    Static Keys             As New Collection
 
    On Error GoTo Err_RandomRowNumber
   
    If Reset = True Then
        Set Keys = Nothing
    Else
        Keys.Add Rnd(-Timer * Keys.Count), Key
    End If
   
    RandomRowNumber = Keys(Key)
   
Exit_RandomRowNumber:
    Exit Function
   
Err_RandomRowNumber:
    Select Case Err
        Case KeyIsInUse
            ' Key is present.
            Resume Next
        Case Else
            ' Some other error.
            Resume Exit_RandomRowNumber
    End Select
End Function

Now, create a query like this - again using the table from the attached demo database:


SELECT
    ID,
    [Order ID],
    [Unit Price],
    RandomRowNumber(CStr([ID])) AS RandowRow
FROM
    [Order Details]
ORDER BY
    RandomRowNumber(CStr([ID]));

Note, that you can change ordering and filtering back and forth - you can even close the query and open again - the values of RandomRow will remain.


Often, however, you will want the numbers of RandomRow to regenerate whenever the query is run. To obtain this, call the function to reset the numbers either before or after running the query:


RandomRowNumber vbNullString, True

You may feel tempted to include that in the query for an automatic reset when the query is run (the WHERE clause):


SELECT
    ID,
    [Order ID],
    [Unit Price],
    RandomRowNumber(CStr([ID])) AS RandowRow
FROM
    [Order Details]
WHERE 
    RandomRowNumber(CStr([ID]))<>RandomRowNumber("",True)
ORDER BY
    RandomRowNumber(CStr([ID]));

but the drawback is, that now the query will behave like the query listed under the previous paragraph; it will regenerate the numbers whenever you change sorting or filtering or requery the query.


Random records in a form

This is the typical use for random numbers: List a count of randomly selected items.


For example, to list ten random products from the Northwind 2007 sample database, create this query:


SELECT TOP 10 
    *, 
    RandomRowNumber(CStr([ID])) AS RandomRow, 
    Rnd(-Timer()*[ID]) AS RandomRecord
FROM 
    Products 
ORDER BY 
    RandomRowNumber(CStr([ID]));

and use it as RecordSource for a form.


Note please, that the expression for RandomRecord is included as well. Its only purpose here is to demonstrate that is often will requery, while RandomRow does not.


The form may look like this:



Note the sorting on field Row causing all other fields to appear in random order.


A form of this type displays a selection only. For many reasons, you may wish to view another selection. A normal requery will - as explained - not change it, so a dedicated button is included: Reset.


Clicking this runs this code:


Private Sub ResetRandomButton_Click()

    RandomRowNumber vbNullString, True
    Me.Requery

End Sub

First, it resets the numbers, then it requeries the form and its RecordSource, the query.


The query will again call the function RandomNumber for each record, causing the numbers to be rebuilt. As the numbers will be different from the previous values, and the form is ordered by these numbers, the displayed selection of records will change.


Conclusion

With these functions and example queries - and the basics on how pseudo-random numbers are handled by VBA - you should have a solid foundation for selecting the right tools and methods when records are to be randomly selected or ordered in an application.

Combined with the second part on sequential enumeration, they are supposed to cover every real-life need.


Further information

The random numbering is part of a project on the general numbering of records.

Sequential ordering is covered here: Sequential Rows in Microsoft Access


A sample database in Access 2016 is attached: RowNumbers 1.2.0.zip

All code can also be found on GitHub : VBA.RowNumbers



0
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free