I have designed an Access 2000 database to store multiple choice
questions and answers which are stored within one table.  Each
question has five possible answers, and these are stored in a table
along with the question and answers for the distractors (other
possible answers but not correct ones).  I can make a nice report that
prints out the questions, but this always
gives the correct answer as option A.  Making it easy for a student to
quickly work out that the first answer is always the right one. I have
done some VBA programming before but very crudely as I am not a real
programmer.  I think this requires using some sort of random function
and then putting it into a form or report but I don't know how to do
that!

(i) How can I randomly select questions from the database, say perhaps
50 from a possible 200; and, (ii) then produce a report that will
randomly place the correct answer amongst the incorrect ones so that I
could print it out on paper for a student exam?

---

Request for Question Clarification by answerguru-ga on 02 Oct 2004 15:32 PDT

Hello Andrew,

I know why you are currently unable to achieve the result you are
looking for, but to be honest, it would take me at least an hour or
two to describe the solution. I think you might want to consider
adjusting your list price, as other researchers are likely to feel the
same way.

If you are looking for more specific answers, you might also want to
provide the names of the tables and their columns.

Thanks,
Chico

---

Clarification of Question by andrewp2003-ga on 03 Oct 2004 08:19 PDT

What do you think is a fair price for this?  It may just be easier if
I send you the database as it is so far and make changes directly to
that.  I am just not familiar enough with ADO and recordset stuff.

Andrew

---

Request for Question Clarification by answerguru-ga on 03 Oct 2004 10:38 PDT

Hi Andrew,

I think what will really get you on the right track is if we
redesigned you database table so that it would facilitate the type of
manipulation you were describing. What I can provide for you is:

1. A description of the tables and columns within those tables
2. The relationships to create between the tables
3. A query to randomly select 50 questions from your set of questions
(with all the possible answers)
4. A query that retrieves the same 50 questions with only the correct
answer (you can use this to provide the set of solutions)

I would say that a fair price for this amount of work would be in the
$150-$200 range.

While I would not mind working directly with your DB file, this forum
does not support file upload. If you could provide your current file
by uploading it elsewhere and providing a link, that would be fine.
However, you will need to follow the instructions I provide you to
re-design the table.

Now, this is going to be tricky if you have already put all your
information into the DB. The best situation is if we can create a new
design from scratch which you can re-enter your data *after* the
design is complete.

Let me know how you would like to proceed.

Thanks,
answerguru-ga

Hi, Andrew. answerguru is right, unfortunately. However, you could do
a dirty little workaround. Throw your data out to Excel. Add a column
and put an X next to the correct answer. Use =rand() to sort the
answers and distractors in a random order. (Note there may be a bug if
you have 2003: http://www.pcmag.com/article2/0,1759,1544551,00.asp)

Then import the data back into Access and run a report.

If you constantly need new random order of distractors, then yes, VBA
is your answer. Or perhaps you can just use this?
http://support.microsoft.com/?kbid=208855

Good luck to you! (I hate Access. I am an Excel and Word geek. But I
was involved in the development of about a half-dozen tests for
www.Brainbench.com, and we do them in Access databases.)

Hi,
I have done sampling in Access using some very easy vba function for
dealing with Randomness.
I'll try to describe as much as i can here... last time i used it was years ago.

To Return 50 Random Rows:
--------------------------
1. The Rnd() function returns a random number.
2. Create a query like: SELECT Rnd() AS F3, Table1.field1 FROM Table1
   The second field will be a random number. 
   However, since the function does not have parameter, vba thinks
that nothing changes, so it is constant, that is 'no need to call the
function for every record'. As a result, the same number appear on
each row.
3. To bypass the above problem, in a vba module add the function:

   Public Function MyRnd(Val1 As Variant) As Double
      MyRnd = Rnd()
   End Function

   Now try : SELECT MyRnd([Field1]) AS F3, Table1.field1 FROM Table1
   Sort MyRnd() Descending
   With Properties (menu View > Properties) TopValue : 50

   Run the query and you can see it returns only 50 records based on
the Descending random number.

4. One thing about the above function is that it the Val1 parameter is not used.
   If we had defined MyRnd as 
             ...MyRnd() As....
   Since it has no parameter, as with the Rnd() it would not be called
for each record.
   Adding a parameter makes vba call the function because a parameter
means the function varies.... or something like that.

ANYWAY, this should take 2 minutes to implement.

I haven't given a thought for mixing the 'questions' order within a record yet.

I hope this helps,
Regards,
Sebastien