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