Unique Random Integer

[reysy28]

Hello Guys,

I have this small problem that I can't solve, hope that somebody will help me on this. I have a one table that has a one field call QuestionID (Integer), I would like to random an integer number from 1 to 100 and save 50 unique numbers on my table. At the end I will be having 50 records with unique numbers. Hoping that somebody will help me on this, am a newbie in ms access....

 

[pr2-eugin]

Hello, Welcome to AWF

Getting a Random number is not a problem, check this link that explains it all : http://www.techonthenet.com/excel/formulas/rnd.php

However for that to be unique, it might need some work to be put it. Probably some looping code:?

 

[reysy28]

Hello, Welcome to AWF

Getting a Random number is not a problem, check this link that explains it all :

However for that to be unique, it might need some work to be put it. Probably some looping code:?

Thanks Pr2-Eugin,

I already read that but the problem is how to write the code and to check if it is unique or not and how to save it on my table....

 

[pr2-eugin]

Okay explain me a few things.

1. Why not simply use 1-50 in a sequential order? They will be unique and does not need any complex coding.
2. If I do write a code that can achieve this, the code will fail after its second run. 50 "random" numbers on the first run, then the remaining 50 in the next run. The third run would be an infinite loop as number between 1-100 would have been used up.

 

[reysy28]

Okay explain me a few things.

1. Why not simply use 1-50 in a sequential order? They will be unique and does not need any complex coding.
2. If I do write a code that can achieve this, the code will fail after its second run. 50 "random" numbers on the first run, then the remaining 50 in the next run. The third run would be an infinite loop as number between 1-100 would have been used up.

The idea is this...... we have 100 questions and I need to get 50 questions in random order...

 

[jdraw]

You really have 2 tasks.
1-- uniquely number each question in a set of questions
2 --select x random questions for some purpose.

So as Paul says - use sequential numbering 1-50 or 1-200 or whatever to get a set of numbered questions. Then, use a technique to get x random numbers from your set of questions.

Here's a link to a sample selecting a random set of records from a large set of records.
Not exactly what you're asking, but has the concept.

 

[stopher]

Here's a function that returns a random list of indexes as a collection:

Public Function GetRandomList(required As Integer, population As Integer) As Collection
    Dim i As Integer
    Randomize
    
    Set GetRandomList = New Collection
    
    'set up a full list
    For i = 1 To population
        GetRandomList.Add i
    Next i

    'randomly remove members until "required" is reached
    Do While GetRandomList.Count > required
        GetRandomList.Remove (Int((GetRandomList.Count * Rnd) + 1))
    Loop

End Function

Here's an example of how to call it:

Public Sub test()
    Dim myrandomlist As Collection
    Dim i As Integer

    Set myrandomlist = GetRandomList(20, 100)
    
    'output
    For i = 1 To myrandomlist.Count
        Debug.Print i; myrandomlist.Item(i)
    Next i
    
End Sub

And sample output from the above (20 indexes from 100):
1 1
2 2
3 4
4 11
5 14
6 15
7 20
8 26
9 32
10 38
11 61
12 67
13 68
14 73
15 75
16 78
17 92
18 94
19 95
20 96

 

[reysy28]

Thanks Stopher, it works great, now I can finish my questionaire system...