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.
Asked
Active
Viewed 5.5k times
3 Answers
26
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.
-
2Funny 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](http://database.ittoolbox.com/groups/technical-functional/access-l/random-number-generator-query-not-so-random-3887838) – kobik Oct 21 '13 at 11:41
-
it does same thing bummi, each time i run it i get the same 4 results – Glen Morse Oct 21 '13 at 11:59
-
so how you suggest to fix @Kobik – Glen Morse Oct 21 '13 at 12:00
-
8@GlenMorse, There is a working workaround (Bummi, you might wan to edit your answer): [ORDER BY NEWID() in MS Access](http://oxle.com/topic.asp?archive=true&tid=3514). so using a negative number with `Rnd()` "fixes" the problem. use: `ORDER BY Rnd(-(1000*ID)*Time())` – kobik Oct 21 '13 at 12:04
16
The following will get a random questionID from your table
MySQL
SELECT questionID FROM questions ORDER BY RAND() LIMIT 1
MS Access
SELECT top 1 questionID from questions ORDER BY rnd(questionID)

SuperRod
- 557
- 3
- 8
-
8the version for MS Access, does seem to generate random records - but the same ones each time! – kneidels Aug 05 '13 at 13:33
-
1
4
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.

Yauhun
- 103
- 2
- 9
-
This one works perfectly okay for me. All other methods gives the same set of records. Good work. – MatVAD Nov 01 '17 at 06:19