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.

Quadragesima

Answer 1

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)

Indetermination

the version for MS Access, does seem to generate random records - but the same ones each time! – Udella

AGREE.. same random records – Talmudist

Answer 2

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.

Trafficator

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 – Waterless

it does same thing bummi, each time i run it i get the same 4 results – Talmudist

so how you suggest to fix @Kobik – Talmudist

@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()) – Waterless

Answer 3

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.

Conic

This one works perfectly okay for me. All other methods gives the same set of records. Good work. – Voigt