While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.
This is the first article on row numbers in Microsoft Access.
The second is about Sequential Rows in Microsoft Access.
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.
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:
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:
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
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.
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.
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.
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.
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.
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