Solved How to get random record from MS Access database

[MajP]

o, basically, my question is was it necessary to call the Randomize() function for every record in the table?

If I said that it was a mistake. You only have to call it once before you try to get the Random numbers.

 

[theDBguy]

Yeah, no Idea what that is. Is the function posted? My guess it is just a code to randomize.

My point is it depends on what you are doing. If you are doing the weekly drug test then you better randomize. But sometimes you absolutely do not want to do that. You want to verify your code. You may want random numbers, but you may want to repeat the steps.
It is not a bug it is a feature. Everyone thinks it is a bug, but if this is something you do a lot you want it repeatable.

Hi MajP. Yes, it's in the link I posted earlier. Here's a copy of the function:

'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Function Randomizer () As Integer
Static AlreadyDone As Integer
        If AlreadyDone = False Then Randomize : AlreadyDone = True
    Randomizer = 0
End Function
'************ Code End *************

And here's how Dev said to use it in a query:

select top 100 mytable.*from mytable
where randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)

But like you said in another thread, although I don't fully understand it, I just use it, because it works.

Edit: PS. I bolded the parts I need help explaining. Thanks again!
Edit: Looks like bolding doesn't work inside a code tag.

So, basically, this part: where randomizer() = 0 and this one: order by rnd(isnull(mytable.question) * 0 + 1)
Cheers!

 

[Dreamweaver]

Yes as long as the db is open you will continue to start from a new position. Now close the db. You will get the same series. If not I can give you databases that will show this.
This is not a debateable issue. MS will clearly explain this. I did not make this up and it makes total sense. If you want a new series you have to randomize.

Thanks @MajP Didn't know that for the project I'm working on it wont matter as I have other filters going on

 

[MajP]

I'm sure it is explainable and absolutely done on purpose; otherwise, they would have probably given a simpler solution on the website. However, they neglected to provide that explanation, and I just never thought to ask about it before. So, can you break it down for us? Thanks again!

Most people who need RVs want to run an experiment with random numbers. But they want to come back and verify their results using the same set of random numbers,. That is what many people find confusing.

 

[theDBguy]

Most people who need RVs want to run an experiment with random numbers. But they want to come back and verify their results using the same set of random numbers,. That is what many people find confusing.

Hi MajP. Sorry for the confusion, but you seem to keep going back to the Randomize statement, which I already admitted I understood and agree that it's necessary. However, my original confusion was with the Randomizer() function. Unless you're saying they are basically doing the same job. Is that it? Thanks!

 

[jdraw]

I was talking of the Randomize statement to reset the seed value for each value.

 

[theDBguy]

I was talking of the Randomize statement to reset the seed value for each value.

Thanks for the clarification. Doesn't the Rnd() function accept a "seed" value also? Is that a different seed? Thanks!

 

[jdraw]

??Possibly, I was taught early on that RANDOMIZE was the best method to get random records for evry iteration/program execution.
Some info here.

 

[cheekybuddha]

@DBG, it appears it allows for Randomize() to be called just once per query call.

 

[theDBguy]

@DBG, it appears it allows for Randomize() to be called just once per query call.

Hi. Thanks. Okay, got that. But why use the WHERE Clause? Would it have done the same job if called as a calculated column or even in the ORDER BY Clause? Like I said, I have no desire to change it, and I will continue to use it, because it works as is. I just wish I could get a little bit of help in understanding it. If not, it's okay too. Cheers!

 

[cheekybuddha]

I guess it needs to be done in the WHERE clause so that it occurs before any call to Rnd() takes place. Randomizer() returns 0, so all records will be available to be selected from.

If you call it from a calculated field then Rnd() in the ORDER BY will have already been called (necessary to determine which records to select)

It could be possible to re-write the function:

Function Randomizer(vVal) As Variant

  Static Randomized As Boolean
 
  If Not Randomized Then
    Randomize()
    Randomized = True
  End If
  Randomizer = Rnd(vVal)

End Function

Then call it in the ORDER BY:

-- ...
ORDER BY Randomizer(ID);

Just guessing.

 

[theDBguy]

I guess it needs to be done in the WHERE clause so that it occurs before any call to Rnd() takes place. Randomizer() returns 0, so all records will be available to be selected from.

If you call it from a calculated field then Rnd() in the ORDER BY will have already been called (necessary to determine which records to select)

It could be possible to re-write the function:

Function Randomizer(vVal) As Variant

  Static Randomized As Boolean

  If Not Randomized Then
    Randomize()
    Randomized = True
  End If
  Randomizer = Rnd(vVal)

End Function

Then call it in the ORDER BY:

-- ...
ORDER BY Randomizer(ID);

Just guessing.

Hi. Thanks. Makes sense. However, just to double-check, according to this MS Docs, the SELECT clause is executed/processed before the ORDER BY clause. If so, do you think it still won't work as a calculated column? Still, I like your updated function. Cheers!

 

[cheekybuddha]

You'll have to test and let us know what you find.

Maybe a new blog post brewing ...

 

[theDBguy]

You'll have to test and let us know what you find.

Maybe a new blog post brewing ...

Hi. LOL. You know I was just speculating, right? Of course one advantage of using the WHERE clause is the result of the function doesn't clutter the actual fields needed by the user from the table. I could test your new function in the ORDER BY clause though.

 

[cheekybuddha]

Haha! You know I'm just being lazy! You got me curious, and I'm just trying to get you to do the donkey-work!!

 

[Dreamweaver]

I'd be interested in your results @theDBguy

 

[MajP]

Randomizer()

function. Unless you're saying they are basically doing the same job. Is that it?

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.

 

[cheekybuddha]

>> My only point was manyAccess developers think this is a shortcoming or a bug <<

Only if they haven't read the Help file!

 

[MajP]

Only if they haven't read the Help file!

Not really that as much as expectations. The average guy would want a random draw every time, would not make much sense otherwise. They would probably think it is weird to want a repeatable random sequence. The analyst on the other hand would want this to be repeatable.

 

[The_Doc_Man]

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.