How can I return Random records from a table? 1

[Trinitri]

I try to get random question from a table. For that I use a query.

I found one answer to my question on but doesn't work.

***********************************************

http://www.mvps.org/access/queries/qry0011.htm

Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function

Now to get 100 questions picked at random:

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

This doesn't work for me because when I close the program and I run it again the app start to give the same sequency of records again every time. Please if anybody have any idea and can help me.
thank you for your time and your help

Trinitri

 

[AvGuy]

Of course you will get the same sequence each time you run the routine. To get a different sequence you need a seed in your formula that will be different each time. Try using the current time as a seed; multiply it by the random number and extract the first 2 significant digits of the integer portion and that should do it for you.

AvGuy

 

[Trinitri]

Thanks, but I'm not very familiar with the use of random functions. I'm not see very clear how is working yet. It supose that I must to set the seed on the function Randomizer when I use Randomize, because I read that use the function Randomize before Rnd will change the seed every time, I don't know for me is very confuse. Can you please explain me how this really works. Thank you for your answer I will try to figure out how to do it.
Thanks
Trinitri

 

[Trinitri]

Well I used your sugestion but doesn't work either. I generated a random number using the las two digits of the current time multiply by a random number, even I change the code and I picked the questions one by one, doesn't work , each time that I run the routine I get the same sequency. I spend all the day trying diferents solutions, I don't know my brain is dead, please any sugestion.
thanks in advance
Trinitri

 

[Uriel365]

This is a bit long winded but is the easiest way I have of returning random records from a table.
- Create an exact copy of your existing table minus the data.
- From this new table remove any auto number field and create a new autonumber field and set its New Values property to 'Randomise' instead of 'Increment'.

- Then before you call your query call an update table query that copies all the data from the original table into the new table. Access will give each record imported a new random autonumber.

- If you base your query on this new table and use the sort property of the autonumber field you will get a random ordered return. I am guessing you are using the 'TOP VALUES to return a set number.

All that you have to do is ensure that the data in the table is deleted and re imported each time you run your query.

Hope this helps

Paul