On 16 Sep 2004 11:47:59 -0700,
gj******@hotmail.com (G.J. v.d. Kamp)
wrote:
Here's what i came up with:
I assume the table with the questions has the following format:
Id
Question
RightAnswer
WrongAnswer1
WrongAnswer2
WrongAnswer3
Now the following query will give you the answers to question 1 in
random order:
SELECT Answer, Correct
FROM
(SELECT RND() AS Position, RightAnswer AS Answer, True AS Correct FROM
Questions WHERE Id = 1
UNION ALL
SELECT RND() AS Position, WrongAnswer1, False AS Correct FROM
Questions WHERE Id = 1
UNION ALL
SELECT RND() AS Position, WrongAnswer2, False AS Correct FROM
Questions WHERE Id = 1
UNION ALL SELECT RND() AS Position, WrongAnswer3, False AS Correct
FROM Questions WHERE Id = 1)
ORDER BY Position;
I hardcoded question 1 in there to keep it simple, when you open this
in VBA you replace that with the Id of the question you want to ask.
It almost as lengthy as the other alternative, it just looks more
'pure' to me.
Regards,
GJ
Once again, an interesting approach.
But if it's elegance and generality that you are looking for, you
can't beat recursion!
Here's a recursive class (in VB6) that can permute an arbitrarily
sized collection of arbitrary objects.
You can't get more general than that!
It's not all that long, considering what it does.
(If you want to translate this into VB.net, be careful about the
different way collections are based)
'Class: Permutations
'(C) Copyright 2001, Michael Gray
' Generate permutations of the CollectionsToPermute in the
CollectionsToPermute collection.
' Return the result through a collection of collections that each hold
a unique permutation.
Property Get GeneratePermutations(ByVal CollectionsToPermute As
Collection) As Collection
Dim num_CollectionsToPermute As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim FirstValue As Variant
Dim NewPermutations As Collection
Dim ResultCollection As Collection
Dim NewResultCollection As Collection
' See if there is only one value.
If CollectionsToPermute.Count = 1 Then
' Return a collection containing one permutation equal to the
single value.
Set ResultCollection = New Collection
ResultCollection.Add New Collection
ResultCollection.Item(1).Add CollectionsToPermute.Item(1)
Set GeneratePermutations = ResultCollection
Exit Property
End If
' Build permutations starting with each possible first item.
Set ResultCollection = New Collection
num_CollectionsToPermute = CollectionsToPermute.Count
For I = 1 To num_CollectionsToPermute
' Save this value.
FirstValue = CollectionsToPermute.Item(I)
' Remove the item.
CollectionsToPermute.Remove I
' Generate the permutations of the remaining
CollectionsToPermute.
Set NewPermutations =
GeneratePermutations(CollectionsToPermute)
' Make permutations by adding FirstValue to the beginning of
each of the new permutations.
For J = 1 To NewPermutations.Count
' Add the first item.
Set NewResultCollection = New Collection
NewResultCollection.Add FirstValue
' Add the rest of the items in the jth new permutation.
For K = 1 To NewPermutations(J).Count
NewResultCollection.Add NewPermutations(J).Item(K)
Next K
' Add this new permutation to the ResultCollection.
ResultCollection.Add NewResultCollection
Next J
' Restore the removed value.
If I > CollectionsToPermute.Count Then
CollectionsToPermute.Add FirstValue
Else
CollectionsToPermute.Add FirstValue, , I
End If
Next I
' Return the ResultCollection.
Set GeneratePermutations = ResultCollection
End Property
