How to get random record from MS Access database

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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)