Question Banks?

[dcfwilde]

Is there any way that one could use Access to make a question bank? For
example, a database of, say, 100 questions, and being able to select 30
random questions each time?

I'm not sure if Access could select 'randomly'.

Thanks for the help.

 

[Douglas J. Steele]

http://www.mvps.org/access/queries/qry0011.htm at "The Access Web" shows how
to select random records.

 

[Arvin Meyer [MVP]]

Here is some untested Randomizing code yo get you started. Build yourself a
table of questions:

Sub RandomQuestion()
On Error GoTo Error_Handler

Dim intRnd As Integer
Dim intQCount As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("tblQuestions", dbOpenDynaset)

Randomize Timer
rst.MoveLast
intQCount = rst.RecordCount
intRnd = Rnd * intQCount
rst.MoveFirst
rst.Move intRnd

Me.txtQuestion = rst![Question]

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[Ivan]

When you are building the code for random selection, you have to keep in mind
whether you want the same question to be able to come up twice in the list of
30. If you do not, there is more code required.

Here is some untested Randomizing code yo get you started. Build yourself a
table of questions:

Sub RandomQuestion()
On Error GoTo Error_Handler

Dim intRnd As Integer
Dim intQCount As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("tblQuestions", dbOpenDynaset)

Randomize Timer
rst.MoveLast
intQCount = rst.RecordCount
intRnd = Rnd * intQCount
rst.MoveFirst
rst.Move intRnd

Me.txtQuestion = rst![Question]

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there any way that one could use Access to make a question bank? For
example, a database of, say, 100 questions, and being able to select 30
random questions each time?

I'm not sure if Access could select 'randomly'.

Thanks for the help.

 

[John Spencer]

With relatively small number of records (up to a few thousand), the odds of
getting a duplicate are really really really small.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

When you are building the code for random selection, you have to keep in mind
whether you want the same question to be able to come up twice in the list of
30. If you do not, there is more code required.

Here is some untested Randomizing code yo get you started. Build yourself a
table of questions:

Sub RandomQuestion()
On Error GoTo Error_Handler

Dim intRnd As Integer
Dim intQCount As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("tblQuestions", dbOpenDynaset)

Randomize Timer
rst.MoveLast
intQCount = rst.RecordCount
intRnd = Rnd * intQCount
rst.MoveFirst
rst.Move intRnd

Me.txtQuestion = rst![Question]

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is there any way that one could use Access to make a question bank? For
example, a database of, say, 100 questions, and being able to select 30
random questions each time?

I'm not sure if Access could select 'randomly'.

Thanks for the help.

 

[James A. Fortune]

With relatively small number of records (up to a few thousand), the odds
of getting a duplicate are really really really small.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Without getting into a debate about what constitutes "really really
really small," if my calculations are correct, the odds of randomly
selecting at least one duplicate question when selecting 30 questions
with replacement out of a pool of 100 is approximately 99.2%. For 30
questions out of a pool of 2000, I get 19.6% - much higher than
intuition might falsely suggest given the subtle nature of the
hypergeometric distribution.

Public Function P2(n As Integer, d As Integer) As Double
Dim lngI As Long
Dim dblTemp As Double

dblTemp = 1
For lngI = d - 1 To d - n + 1 Step -1
dblTemp = dblTemp * lngI / CDbl(d)
Next lngI
P2 = 1 - dblTemp
End Function

MsgBox(P2(23, 365)) => 50.7297%
MsgBox(P2(30, 100)) => 99.2209%
MsgBox(P2(30, 2000)) => 19.6339%

C.f.:

http://mathworld.wolfram.com/BirthdayProblem.html

Note that the value for P2(23, 365) is exactly the same as the result on
the Wolfram page. An interesting bit of trivia on the same page:

"...the distribution of birthdays is assumed to be uniform throughout
the year (in actuality, there is a more than 6% increase from the
average in September in the United States; Peterson 1998), then ..."

Note that I was careful not to push the function above to its numerical
limits since it would not be difficult to find values of n and d that
would compromise the accuracy of the function's result. Suffice it to
say that based on the preliminary function results shown above, I
recommend that some method be used to preclude the selection of the same
question twice, subject to verification of the function's algorithm and
accuracy.

James A. Fortune
(e-mail address removed)

 

[John Spencer]

Different assumptions: You are assuming that each question gets assigned a
number from 1 to X (X being the largest number of records). I assume that
each question gets assigned a random number >0 and less than 1.

My pool of random numbers is quite large. And I assign a number to each record
from that large pool.

So if I use a query where each record gets assigned a random value and then
use that random number to select the top 30 out of 100 I am very likely to get
no duplicates.

Assumptions:
== QuestionID is a number field and is never null.
== Randomize has been executed to force a different sequence to be generated

SELECT TOP 30 *
FROM TblQuestions
ORDER BY rnd(QuestionID)

John Vinson has posted this VBA function in the past.

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Using it simplifies the process.
SELECT DISTINCT TOP 30 *
FROM TblQuestions
ORDER BY RndNum(QuestionID)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

Different assumptions: You are assuming that each question gets
assigned a number from 1 to X (X being the largest number of records).
I assume that each question gets assigned a random number >0 and less
than 1.

My pool of random numbers is quite large. And I assign a number to each
record from that large pool.

So if I use a query where each record gets assigned a random value and
then use that random number to select the top 30 out of 100 I am very
likely to get no duplicates.

Assumptions:
== QuestionID is a number field and is never null.
== Randomize has been executed to force a different sequence to be
generated

SELECT TOP 30 *
FROM TblQuestions
ORDER BY rnd(QuestionID)

John Vinson has posted this VBA function in the past.

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Using it simplifies the process.
SELECT DISTINCT TOP 30 *
FROM TblQuestions
ORDER BY RndNum(QuestionID)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I agree that the amount of numbers between 0 and 1 is indeed sufficient
with your technique to make the chances of getting a duplicate very very
(several more very's) small. But why take any chance at all to get
duplicates when it is possible to guarantee that you get don't get any
duplicates with a little more work? There is a large difference between

0.999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999742

and

1.0

in statistical and philosophical senses. It helps me sleep better at
night . I suppose since the problem doesn't involve public safety
and that the penalty for getting a duplicate question is minimal, you
can "probably" convince the customer that the statistical expectation
for the cost of an error is quite inexpensive. The time required to do
the calculation doesn't seem to be a factor so the simplification of the
algorithm isn't buying much except for the virtues of simplification
itself. Personally, I like to make the chances of making an error zero
whenever possible, even at the cost of having a little more code. My
mathematical experience, especially in crafting proofs, overshadows my
engineering judgment in such matters. But you seem to be aware of the
trade-offs you are making. That is all I ask.

James A. Fortune
(e-mail address removed)