Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
I have a MS access database.
In that, one table consists of questions and answers with primary key questionID.
I need to retrieve random question from that table using questionID.
What keywords or query should I use for this scenario.
SELECT TOP 5 questionID FROM [tableName] ORDER BY rnd(INT(NOW*questionID)-NOW*questionID)
This will give you a new set of answers every time, you don't even need to make up a time when you use "NOW" (which will every time be a new time you click this no matter how fast you click), in my opinion the most simple and neat way to solve this in Access.
This one works perfectly okay for me. All other methods gives the same set of records. Good work.
To get different random record you can use, which would require a ID field in your table
SELECT TOP 1 questionID FROM questions ORDER BY Rnd(-(100000*questionID)*Time())
A negative value passed as parameter to the Rnd-function will deliver the first random value from the generator using this parameter as start value. (A kind of defined randomize).
Special thanks to @kobik 's hint from the comments.
Funny thing. I have tested all suggested answers including this I get different/random records. but as soon as I run my test program again the results are in the same sequence as before. as if the random seed generator resets each time in ms-access. I later found this: Random Number Generator Query Not So Random
it does same thing bummi, each time i run it i get the same 4 results
so how you suggest to fix @Kobik
@GlenMorse, There is a working workaround (Bummi, you might wan to edit your answer): ORDER BY NEWID() in MS Access. so using a negative number with Rnd() "fixes" the problem. use: ORDER BY Rnd(-(1000*ID)*Time())
ORDER BY Rnd(-(1000*ID)*Time())
The following will get a random questionID from your table
SELECT questionID FROM questions ORDER BY RAND() LIMIT 1
SELECT top 1 questionID from questions ORDER BY rnd(questionID)
the version for MS Access, does seem to generate random records - but the same ones each time!
AGREE.. same random records