Solved How to get random record from MS Access database

[theDBguy]

Sorry for any confusion, I did not look at this randomizer. I have to guess there is a randomize in there.

My only point was manyAccess developers think this is a shortcoming or a bug. But those who rely on random numbers want to be able to return the same series of random numbers.

Okay, I think you and cheekybuddha helped me understand the custom function a bit better now. So, how about that Order By clause? What was up with all that math and the IsNull() function? Anyone care to tackle that one? Thanks again.

order by rnd(isnull(mytable.question) * 0 + 1)

 

[cheekybuddha]

Surely it's the same as:
ORDER BY Rnd(1)
?

 

[theDBguy]

Surely it's the same as:
ORDER BY Rnd(1)
?

I know, right? What were they thinking?

By the way, I did a few experiments. Using the original setup and query from The Access Web with the following SQL:

SELECT Table1.id, randomizer() AS WhereClause, Rnd(IsNull([id])*0+1) AS OrderByClause
FROM Table1
WHERE (((randomizer())=0))
ORDER BY Rnd(IsNull([id])*0+1);

I get the following result (we all know this works already):

Using your modified Randomizer() function and the following SQL:

SELECT Table1.id, frandomizer([id]) AS OrderByClause
FROM Table1
ORDER BY frandomizer([id]);

I got the following result:

And using the original setup and the following SQL (where I put the call to the function as a calculated column:

SELECT Table1.id, Randomizer() AS Randomizer, Rnd(IsNull([id])*0+1) AS OrderBy
FROM Table1
ORDER BY Rnd(IsNull([id])*0+1);

I got the following result:

So, in conclusion, all approaches work to generate a random record order to use for a SELECT TOP query. However, I think I will adapt cheekybuddha's simplified function because I think it's easier to understand. The only thing I had to change is I declared your function as a Double, because using a Variant seems to have resulted in a String value.

Thanks for the assist, everyone! Cheers!

 

[The_Doc_Man]

Not that you can't use a DOUBLE for this, but I believe that RND() normally only returns a SINGLE. It might get converted to a DOUBLE by the assignment but it still probably will only have about 21 or 22 bits of functional mantissa like the SINGLE from which it was derived. Even though a DOUBLE has 53 bits. It would be possible to build a "true" DOUBLE random function if you wanted to but it all depends on the modulus when using "standard" randomization.

Don't forget that when looking at those numbers that SEEM to go on forever, your real issue is that you have a binary fraction being expressed in a decimal format, and that's an oil-and-water mixture. Decimal fractions are based on 0.1 or 1/10, which factors as 1/2 and 1/5. Now, 1/2 in binary is trivial. But 1/5 is a repeating binary so cannot be expressed exactly. And that means the reverse operation won't be exact either. If you were to look at the binary or hexadecimal output of the RND() function, you would probably be able to see how many bits were involved with regard to the modulus.

 

[Dreamweaver]

Surely it's the same as:
ORDER BY Rnd(1)
?

Sorry @The_Doc_Man to clarify your using this one?
thanks mick

 

[The_Doc_Man]

@MickJav - Actually, no, wasn't using Rnd(1). If you look up the description of the Rnd() function, using literally Rnd() (with an empty argument) gives you the next random number in a sequence. So does Rnd(1) or any other positive number. But 0 and negative have different effects. I always use the empty-argument case because in another system using BASIC and Rnd() function, the meaning of a number as the argument can also be used to scale the number. So to keep from confusing myself more than normal when I was doing two flavors of BASIC at once, I always used the empty argument case.

Here is the straight skivvy on VBA's RND() function.

Rnd Function - Microsoft Support

support.office.com

 

[Dreamweaver]

Thanks @The_Doc_Man

 

[MajP]

But 0 and negative have different effects. I always use the empty-argument case because in another system using BASIC and Rnd() function

tf you are suggesting doing that in a query, you absolutely can not.

SELECT TOP 10 Products.ID, Products.[Product Name], Rnd() AS Sort
FROM Products
ORDER BY Rnd();

qryNoSeed

ID	Product Name	Sort
20​	Northwind Traders Marmalade	0.37899124622345​
57​	Northwind Traders Ravioli	0.37899124622345​
56​	Northwind Traders Gnocchi	0.37899124622345​
52​	Northwind Traders Long Grain Rice	0.37899124622345​
51​	Northwind Traders Dried Apples	0.37899124622345​
48​	Northwind Traders Chocolate	0.37899124622345​
43​	Northwind Traders Coffee	0.37899124622345​
41​	Northwind Traders Clam Chowder	0.37899124622345​

Because the Rnd() without a changing seed only gets executed once at the very beginning. Other functions are similar. I believe Now will do the say.

 

[MajP]

May find these functions useful. Usually want a random integer in a range, or letter, or integer. However it demonstrates how a constant, empty, or Now() as the seed all fail. It shows that without randomize you can predict the sequence for the upcoming requery. A minor point. For simplicity I call randomize each time the function is used; in truth you would need to only do it once before the query is run. So you could do in in the autoexec and get the same benefit.

Public Function MyRnd(Optional Seed As Variant = 1, Optional NewSeries As Boolean = True) As Double
  'You have to seed it with a unique value per record if used in a query
  If NewSeries Then Randomize
  MyRnd = Rnd(Seed)
End Function
Public Function GetRandomLetter(Optional Seed As Long = 1) As String
  GetRandomLetter = Chr(Int(26 * Rnd(Seed) + 65))
End Function
Public Function GetRandomDigit(Optional Seed As Long = 1) As Integer
  GetRandomDigit = Int(10 * Rnd(Seed))
End Function
Public Function MyRandomLong(Optional Seed As Variant = 1, Optional StartRange As Long = 0, Optional EndRange = 1, Optional NewSeries As Boolean = True) As Long
  'You have to seed it with a unique value per record if used in a query
  'To get a random long between two integers/longs
  Dim Range As Long
    Range = EndRange - StartRange
  MyRandomLong = Range + Int((StartRange + 1) * MyRnd(Seed, NewSeries))
End Function

 

[MajP]

@DocMan
Finally read the Randomizer and that makes total sense to me. As I was saying, in my approach when used in a query my code randomizes on each record, and it really only needs to be done once. So that is what the randomizer code is doing, if already done once then do not need to do it every record or even every time before calling a query.
However, I do not know if there is significant overhead to randomizing each time. It is probably no more than the call to randomizer and the if check.
@isladogs would have to run the performance test to see if there is a difference.
If worried about performance you really only need to call randomize once when you open the database such as autoexec or startup form. That way you are not randomizing on every record or calling a function on each record to check if you randomized.

 

[The_Doc_Man]

tf you are suggesting doing that in a query, you absolutely can not.

​

Because the Rnd() without a changing seed only gets executed once at the very beginning. Other functions are similar. I believe Now will do the say.

Except that you could not use a query to do the random-number update of a field that I mentioned earlier and yet I have done that.

 

[MajP]

Except that you could not use a query to do the random-number update of a field that I mentioned earlier and yet I have done that.

I do not know how you have done that, can you show the query that you used. In the attached db, I show what happens when you provide a seed that is constant, empty, or uses a function like now. You get only one value returned for all records.

 

[Dreamweaver]

What I'm now playing with with min if I have a counter that is incremented on every view that way once a record has been displayed it wont be again until All records have been displayed, I haven't decided if I like if ot not but going to give it a try for a while.

SELECT TOP 1 tblQuestions.QID, tblQuestions.Question, tblQuestions.Answer, tblQuestions.CategoryID, tblQuestions.LevelID, tblQuestions.Qstatus, tblQuestions.Used
FROM tblQuestions
ORDER BY tblQuestions.Used, Rnd([QID]);

 

[Dreamweaver]

I do not know how you have done that, can you show the query that you used. In the attached db, I show what happens when you provide a seed that is constant, empty, or uses a function like now. You get only one value returned for all records.

I did the same and got your result @MajP

 

[MajP]

@MickJav
Not sure how you are doing it, but I append the selections into tblSelected. Then your query could look like

SELECT TOP 1 tblQuestions.QID, .....
FROM
tblQuestions
WHERE QID NOT IN (Select QID from tblSelected)
ORDER BY
tblQuestions.Used, Rnd([QID]);

Then have some code to check if table selected record count = tblselections record count. If so clear tblselections.

 

[Dreamweaver]

On my random dsplay form I have the following Me![Used]=Me![Used]+1

I will be using this when I get the program that far But in relation to teams/Attendees

WHERE QID NOT IN (Select QID from tblSelected)

 

[The_Doc_Man]

Can't find the code where I did that and I finally remembered why I can't find it. I did that for the Navy 10 years ago or more because they wanted a random selection of servers for a security compliance survey they were doing, a selection that would not intentionally repeat servers from one month to the next. They technically owned the code (by contract terms) so I didn't take home a copy. And to be honest, I am no longer sure that I directly used Rnd() in the query. It might have been through some sort of shell function. I tested the direct use myself and sure enough, always the same number. So on that one, I may have been slightly in error as to how to gen up the numbers via query.

 

[The_Doc_Man]

However, I do not know if there is significant overhead to randomizing each time.

This one, I'm fairly certain about. The Randomize function for VBA looks at the time-of-day clock and does that Modulus thing on the milliseconds since midnight to create the seed. So other than the overhead of grabbing the Timer() time and doing a Modulus operation, not much overhead at all I would think.

 

[Dreamweaver]

Hi @The_Doc_Man just tried Rnd(Timer()) which sorry to say didn't work, It does the same as Rnd()

 

[The_Doc_Man]

No, my reference to Timer() is what Randomize does behind the scenes. RND doesn't look at anything in its argument except the condition of its argument as negative, zero, or positive.