Online exam random Questions selection??

Hi m a newbie doing a project in online exams in ASP.NET & MS ACCESS I have the following table containing Questions:
QID(autono),Question,OptionA,OptionB,OptionC,OptionD

and I have one more table Tests:
TestID,QID,Question,OptionA,...OptionD,Duration

Now m showing a form where I enter the number of Questions in the test & duration.I want a random function to pick up Questions from the Questions table(random enuf not to repeat Questions) and insert them to the Tests table(TestID will be provided while creating the test).

Can sum1 help me out with both the above tasks.Its urgent pls help :(

Why are you inserting 'question', optiona, b, c,d into the test table? They already exist in the Questions table and simply the QID should be enough to specify which question you are referring to.

Then,

SELECT TOP 10 * FROM Questions ORDER By NEWID()

Quote:

---

Originally Posted by mendhak

Why are you inserting 'question', optiona, b, c,d into the test table? They already exist in the Questions table and simply the QID should be enough to specify which question you are referring to.

Then,

SELECT TOP 10 * FROM Questions ORDER By NEWID()

---

The reason m duplicating entries is cos I'm letting Questions to be deleted from the questions table,say tomorrow the admin thinks a certain Question should not feature is any test & wants to remove it......... My random question selector might stil select it.Can u please help me on these lines.How should I pick up random Questions?? :confused:

That is not a good way. It breaks your previous tests.

What you should do is to modify your Questions table. Add a new column in there called IsValid. If this column contains a 1, then the question can still be used in tests. If it is 0, then it will not be included in future questions, but you will still know what the question is about when you are looking at previous tests.

This would imply that when an administrator 'deletes' a question it is actually setting the field IsValid to 0.

ok thanx mehendak,thats a nice solution :thumb: once I do that how do i generate random questions also taking into a/c the questions that might be marked for deletion (is valid 0)

SELECT TOP 10 * FROM Questions ORDER By NEWID() WHERE Questions.IsValid = 1

Quote:

---

Originally Posted by mendhak

SELECT TOP 10 * FROM Questions ORDER By NEWID() WHERE Questions.IsValid = 1

---

Thnx for the quick replies dude really appreciate it.Hope I'm not irritating,can u pls tell me how does this above query work to generate randomness :confused:

Well, NEWID() generates a GUID in SQL Server, a new one. GUIDs are pretty big, so you have ensured randomness.

Oh btw as I've mentioned in my first post I'm using MS ACCESS as my back end so wud this work with it ?? :ehh:

Hmm.. .Access. Let me search for this, I'm sure it's pretty similar. Have you tried the above btw?

Ah. As it turns out, it's possible using SQL Statements in Access, but the records aren't very randomized. You can try it with this and see if it suits your needs:

SELECT TOP 10 * FROM Questions ORDER BY Rnd([QID]*0+1);

I cannot verify the randomness of this, but if it does not work, then there is another way. I cannot link you to that page as it has been cached, so I'll paste it here:

Quote:

---

For a _truly_ random selection, a method like this is needed.

Rnd() only returns a pseudo random sequence - and calling Randomize externally doesn't make any difference as Rnd() is run in a different scope within the query.

Thus, for a different selection for any run of the query, the random number must be generated externally after a call to Randomize:

Public Function RandomNumber( _
Optional ByVal booRandomize As Boolean) _
As Single

Static booRandomized As Boolean

If booRandomize = True Or booRandomized = False Then
Randomize
booRandomized = True
End If

RandomNumber = Rnd()

End Function

And then:

SELECT TOP 100
*
FROM
Random_Table
ORDER BY
RandomNumber([Serial_F] Is Null);

The use of the ID in the parameter is needed to call RandomNumber not once but for every record.

Adjust 100 to the number of elements in the selection you wish to make.
Then adjust the query to join the other table.

---

(EE)