Solved How to get random record from MS Access database (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
Actually, if you are using the Rnd() function, one call to Randomize OUTSIDE the query will nonetheless affect the query too, because this is one of those cases where SQL and VBA use the same Rnd function (from the same library). There IS no SQL "Random" function but there is nothing to stop SQL from calling something that is globally available. After all, you can call a public VBA function from a query - so you can call a public library function as well. Therefore, a Randomize that affects one context will affect BOTH contexts.

In the end analysis, ALL purely mathematical random number generators have a "period" i.e. a number of cycles after which the sequence will repeat itself. Most such random numbers are generated using some variant of Xn+1 = ( a * Xn + b ) mod M and the period is related to the value of M. The first value X0 is the seed. Because this is a modulus function, the number of different random values is 2^M for a binary generator. The Randomize function changes the seed value, i.e. the value X0. In so doing, they merely start the sequence from some other point in the cycle. Some folks would warn against using a seed of 0, but because of that "b" term, there is usually no advantage or disadvantage as to where you start. Note, however, that some home-grown functions could run into issues with poor choices of seed, offset, or multiplier. Therefore, it would be better to use commercial functions rather than to "roll your own" unless you are (a) experimenting or (b) well-versed in this kind of theory.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
21,358
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.
Code:
order by rnd(isnull(mytable.question) * 0 + 1)
 

cheekybuddha

AWF VIP
Local time
Today, 17:33
Joined
Jul 21, 2014
Messages
2,237
Surely it's the same as:
ORDER BY Rnd(1)
?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:33
Joined
Oct 29, 2018
Messages
21,358
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:
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):

AW.png


Using your modified Randomizer() function and the following SQL:
SQL:
SELECT Table1.id, frandomizer([id]) AS OrderByClause
FROM Table1
ORDER BY frandomizer([id]);
I got the following result:

CB.png


And using the original setup and the following SQL (where I put the call to the function as a calculated column:
SQL:
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:

DBG.png


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! (y)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
@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.

 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
8,463
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.

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

qryNoSeed

IDProduct NameSort
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

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
8,463
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.
Code:
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
 

Attachments

  • CompareRandomize.zip
    37.3 KB · Views: 128
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
8,463
@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

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
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

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
8,463
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

Well-known member
Local time
Today, 17:33
Joined
Nov 28, 2005
Messages
2,466
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.

SQL:
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

Well-known member
Local time
Today, 17:33
Joined
Nov 28, 2005
Messages
2,466
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

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
8,463
@MickJav
Not sure how you are doing it, but I append the selections into tblSelected. Then your query could look like
Code:
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

Well-known member
Local time
Today, 17:33
Joined
Nov 28, 2005
Messages
2,466
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
Code:
WHERE QID NOT IN (Select QID from tblSelected)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
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

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
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.
 

Users who are viewing this thread

Top Bottom