Random Question generator for MS Access

[MohammadShabbir]

I am use the MS Access Form as frontend and ms-access Database as backend
I want to retrive 10 question randomly from database out of 100 question
simultenously in richtextbox. If i click on next button then second question display.
I am use the four radio button for option
and I click on finish button then show the result and how iam store the 10 question with user answer in database.

[mojyzzzz]

hi mohammad how are u?

I give u the select query to retrieve the top 10 records of a table,
I think the remaining would be so easy for u to do,

good luck

SELECT TOP 10 table1.id, table1.title, Rnd(id) AS Expr1
FROM table1
ORDER BY Rnd(id) DESC;

[zmbd]

MohammadShabbir


- Mojyzzzz's solution will not prevent duplicate questions so it is advisable to check for these or use the "DISTINCT" and "M" has made some assumptions about your table design so be aware of the fact that you may well have to modify the query. ( SQL Predicates )

- As this is a somewhat familiar question often asked for homework or class projects I would ask/require that you post your current code and DB design. Note: that does not mean attach pictures or files as this information can be typed into the post quite easily.
Please remember to format your code and SQL blocks by using the <CODE/> button in the toolbar.

[NeoPa]

You should also bear in mind that the suggested SQL will randomise the data for you, but it will be a repeatable randomisation, such that you will get the same random ten records each time it's run.

[zmbd]

Thank you NeoPa, my oversite on SQL. I usually use VBA and open a recordset for this type of operation when creating my quizes for work.

[mojyzzzz]

If you want to prevent duplication, the only way is to create a table which stores any question ID that is seen by the user,

or the simple but not flexible way is to add a field that works as a flag that represents whether the question is seen or not

[zmbd]

mojyzzzz
I assure you that there are easier ways than either method you've cited to achieve a random record set without duplication.
In fact, one of the very first threads I was involved with at this site dealt with the question of random record selection using weighted probabilities ( https://post.bytes.com/node/840875 ) which ran into just such a discussion.

If there is one thing I have learned over the years is to NEVER say that the "only" way to do some given task is to do it this way or that way.

[TheSmileyCoder]

NeoPa
You should also bear in mind that the suggested SQL will randomise the data for you, but it will be a repeatable randomisation, such that you will get the same random ten records each time it's run.

I would venture that its a bit more complicated then that. The seed number only returns the same result if the seed number is negative,
Example

Rnd(-1)=rnd(-1)
rnd(1)<>rnd(1)

OR the seed has been reset just prior to running the random number generator each time.

[Rabbit]

It should also be noted that Rnd(1) in a select query will return the same number for every row. That is because the function is run only once because the SQL engine assumes a function call with a static parameters will always return the same result.

The only way to get different numbers for each row is to vary the input by incorporating a field value.

[NeoPa]

@Smiley & Rabbit
Well done lads. Both very good points. My post was in error and I overlooked the issue that Rabbit brought up related to SQL optimisations whereby it will determine that the function needn't be re-called as the parameters are the same. It essentially treats it as a constant value once called the first time.

[ADezii]

My approach is similar to zmbd's in that I always use the Recordset approach for generating x number of Random Records for a Data Set of y Records. The general Logic is as follows (using 10 Random Records out of 100):

1. Generate 10 Random Numbers between 1 and 100, storing each in an Array.
2. Loop thru the Array checking for Duplicate Values.
3. Should there be Duplicate Value(s), generate another Random for that Slot.
4. Repeat this process until all 10 Randoms are now Unique.
5. We now have 10, Unique, Random Numbers stored in an Array which now act as Record Pointers to the 100 Record Data Set.
6. Now we can use some mechanism to Navigate to each Record position defined by the Array, and retrieve the appropriate Values.

[zmbd]

I'm afraid I cheat even further than that... I use a class module and use the primary key as the name of the instance. I got the idea from reading thru an example where students and rooms were being handled. At the time it really didn't make much sense when tables would do the job - but then I had a need for the quiz... I was playing with Class modules... already had the jack-hammer out of the toolshed.

The other way I've done this is using a listbox on a form (oh yes, I went there) and then do the loop thingy to check for duplicates much like an array.

In either method... I still open a record set in VBA and pull the random records.

[NeoPa]

What I do is to create an array of integers that reflect all the record numbers (or record offsets) that exist in the dataset. Each element of the array is initially populated with a number matching its own position (IE. Element[0]=0; element[1]=1; element[2]=2; etc). N is set as the number of records.

Now, for each iteration through the loop, a random number (R) is generated such that 0 <= R < 1. R is multiplied by N and the integer portion taken (NOT rounded but truncated.), giving a random integer (X) between 0 and 9 on the first iteration through the loop. Importantly, element[N] is then swapped with element[X]. Lastly, X is decremented (X = X - 1). The last two steps ensure that only unselected values are available for selection next time through the loop.

[zmbd]

That's slick... I've done somthing like that with two list boxes... moved the element from one to the second... just for user touchy-feely :-)

[NeoPa]

Thanks Z. I originally came up with this approach when looking for a way to shuffle cards before dealing (hence the swapping of the elements rather than the simpler moving of [N] to [X]). I struggled to come up with something that would work and not be clumsy, but I only came up with this as a fully formed solution after waking up one day.