14

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.

user1169809
  • 777
  • 3
  • 8
  • 10

3 Answers3

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.

kobik
  • 21,001
  • 4
  • 61
  • 121
bummi
  • 27,123
  • 14
  • 62
  • 101
  • 2
    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](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
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