randomisation help for a total beginner

[fluffyozzy]

Hi everyone,

I have searched the VB forums and have found several very useful pointers but still couldn't solve the problem, so here it is.

I am trying to design a multiple choice test. Each question belongs to a criteria (there are 106 different criteria). Each criteria has 2 or 3 multiple choice questions. First, I would like to choose 1 question from each criteria (producing 106 questions for the pot). Then, would like to randomise these results to produce a table containing 40 questions. So, I need to perform two randomisations within the same function (I think). My question table links to criteria table by CriteriaID field.

I have looked at Mile-O-Phile's multiple choice test database (very useful initially) but I'm very new to programming and cannot seem to figure out what I need to do to first to randomise within a criteria, so I don't get more than 1 question per criteria. At the moment, I only managed to modify Mile's code so it picks up 40 questions from the whole table, which is not exactly what I'm after.

I hope this is clear and I would really appreciate some help on this one. Once I know how to do this, I will be able to apply the same method to other tests I need to do.

Please help.....

 

[ByteMyzer]

For a "total beginner", you're certainly brave, taking on a task which is above the beginner level. Would you be willing to attach a copy of the database with what you have so far (tables with the data for the criteria, etc)? It would make it much easier to advise you how to approach this task for your specific needs.

 

[fluffyozzy]

Hi ByteMyzer,

Thanks for replying so quickly. Tell me about it, this task is totally over my head for the time being. I'm good at modifying clever ready-made code but not good with making my own (yet). Anyway here is the sample of the database that containing the relevant info. I have converted the database to 97 version but it'll only work if you compile the module first (I think). My original database is 2000 version, if this doesn't work, let me know and I'll post the original.

 

[ByteMyzer]

The following solution should provide exactly what you are looking for:

In your MCQRandomisation Module, delete the MakeMCQRandomTable Sub, and use this substitute MCQRandomPick Sub:

Public Sub MCQRandomPick()

Dim intCounterA As Integer, intCounterB As Integer ' Loop Counters
Dim intPosition As Integer                          ' The current random number position
Dim rst As Recordset                               ' Recordset for Random Ranges
ReDim intRandom(0) As Integer                      ' Store for accepted numbers

Set rst = CurrentDb.OpenRecordset("SELECT MCQs.CriteriaID, Min(MCQs.MCQID) AS MinOfMCQID, Max(MCQs.MCQID) AS MaxOfMCQID FROM MCQs GROUP BY MCQs.CriteriaID;")
' MinOfMCQID - Low end of ID range for random selection per CriteriaID
' MaxOfMCQID - High end of ID range for random selection per CriteriaID

Randomize

Do While Not rst.EOF
    ' Inflate the Random Array for new entry
    ReDim Preserve intRandom(UBound(intRandom) + 1)
    ' Randomly select MCQID in the given range for the current CriteriaID and store in Random Array
    intRandom(UBound(intRandom)) = Int((rst!MaxOfMCQID - rst!MinOfMCQID + 1) * Rnd + rst!MinOfMCQID)
    rst.MoveNext
Loop

rst.Close

' Clear the MCQRandom table for new entries
CurrentDb.Execute "DELETE * FROM MCQRandom;"

For intCounterA = 1 To 40
    ' Select Random position within the Random Array
    intPosition = Int(UBound(intRandom) * Rnd + 1)
    ' Fetch MCQID from the Random Array, select matching record from MCQs
    ' and append to MCQRandom
    CurrentDb.Execute "INSERT INTO MCQRandom SELECT MCQs.* FROM MCQs WHERE MCQs.MCQID=" & intRandom(intPosition) & ";"
    ' Remove selected MCQID from the Random Array and deflate by 1
    ' to prevent duplicate selections
    For intCounterB = intPosition To UBound(intRandom) - 1
        intRandom(intCounterB) = intRandom(intCounterB + 1)
    Next intCounterB
    ReDim Preserve intRandom(UBound(intRandom) - 1)
Next intCounterA

End Sub

 

[fluffyozzy]

WOW!!! THANK YOU SO MUCH!!! It's working perfectly.

I know I'm gushing here, but you have no idea how many ways I tried to do this for days!! I am in awe. I would have never come up with that code on my own (maybe after years of study).

Thanks again.

 

[ByteMyzer]

You are most welcome. I'm glad this worked for you.