Random function with a fixed range

[MCAdude]

I would like to have a function where a random value is generated between 1 and 12.so far nothing special. But the tricky part is that each value may only be generated once and after a block of 12 a new block must start.
Example: First block generates the following numbers at random. 1-3-8-10-12-4-5-9-11-2-6-7 (block 1) Next: 9-11-6-3-4-1-7-10-12-5-2-8 (block 2) etc.etc.

Is this even possible in vba in access and can somebody explain how? Thanks in advance..

[This message has been edited by MCAdude (edited 03-15-2002).]

 

[Emohawk]

I would store the random numbers generated in an array of fixed length, 12 items long. As the numbers are generated they are added to the list, however before they are added they are checked to make sure that the number you are adding isn't already in the list, if it is generate a new random number and repeat the process until the list is full. When you reach the end of the list, clear the contents and start again...

Hope this makes sense.

[This message has been edited by Emohawk (edited 03-15-2002).]

 

[David R]

Wellllll, what about making a table with 12 records, one field for the integer and one for a randomly generated number with either RND() or RAND() depending on your version apparently.
Then sort by that random number and you'll have all twelve digits in series every time. You'll have to redo the random number for every series.

It's kind of a bizarro solution but it might be workable.


[This message has been edited by David R (edited 03-15-2002).]

 

[Travis]

Public Function RandomSequence(Num As Integer) As String
Dim ary()
Dim iVal As Integer
Dim iLoop As Integer
Dim iRevLoop As Integer

ReDim ary(1 To Num)
For iLoop = 1 To Num
Randomize (0)
RndNumber:
iVal = Int((Num * Rnd) + 1)
For iRevLoop = iLoop - 1 To 1 Step -1
If iVal = ary(iRevLoop) Then
GoTo RndNumber
End If
Next iRevLoop
ary(iLoop) = iVal
Next iLoop

For iLoop = 1 To Num
RandomSequence = RandomSequence & IIf(RandomSequence <> "", ", ", "") & ary(iLoop)
Next iLoop

End Function

 

[MCAdude]

Thanks Travis for the function, but unfortunatly it's not completly what I had in mind. Perhaps I should explain it a bit more.

Let's say I've got a simple db with 12 records. I've to get 1 block of 12 randomly generated numbers divided amongst those records. But each generated number can only be generated once like I told in my first post.

The following example should make it a bit clearer.

1. John - 11
2. Jack - 9
3. Ben - 7
4. Jay - 2
5. David - 3
6. Larry - 5
7. Harry - 4
8. Barry - 8
9. Jerry - 12
10. Mary - 1
11. Gary - 6
12. Jane - 10

And then a new block of 12 randomly generated numbers should be assigned. Is this still possible?

Sorry for the not so clear first post..HTH

 

[Travis]

Thats what I gave you, The code creates an Array of the random values. For ease of reading I send them back as a string. With A2K I could have used the Join instead, but I wanted to show you how they where stored.

This retrieves your block of twelve random numbers from 1 to 12. Then on the adding of the records you could then use the array to return which on will be appended with what record. Each time you need a new twelve just call the function.

 

[MCAdude]

Sorry for the somewhat late reply, but I checked the function and I noticed I have made an explanation error. The block of 12 should NOT be assigned at once.

The first person would be entered in the db and than a random number from the fixed array of 12 has to be assigned. Than, after 11 more entries a new block has to start, but the numbers have to be the same twelve.

So you see the function I have so far isn't working because it gives the 12 number all at once. But I need a function were the numbers are assigned one at a time with a block of 12.

I hope this clears things up a bit...And again sorry for the faulty explanation