Randomly assign numbers 1-11 to a table

[TOTCOM11]

I have a database which I am using to keep track of a fantasy football draft. I have 11 teams in the league thus far and would like to randomly assign each of these teams a "draft order" number. Each team must have a unique draft number and the numbers must be in the range of 1-11. I have a "Teams" table set up with the following fields:

Team As Text
DraftOrder As Integer

Is there some way I can use a query or VBA code to assign a unique draft number to each of the teams?

TOTCOM11

 

[natatbh]

Hi TOTCOM11,

How about using the Math.rnd function in a loop, like this

draftOrder = Int((11 - 1 + 1) * Rnd + 1)

to generate numbers 1 - 11. Upon getting each number you populate an array with the length of 11. If draftOrder is 1 then array sub 1 should become 1. When you generate the next number let's say it's 4
you check sub 4, if its empty then you populate it and add a field to your table, else continue the loop until the array is full. This way you can randomly get 1 - 11 without duplicates.

natatbh

 

[TOTCOM11]

I've never programmed an array in VBA before, although I have done it in C++...even though it has been awhile. Could you type an example of how this would look?

 

[willir]

TOTCOM11

I really like natatbh solution for calculating the random number.

But I am puzzled. You are using Access to assign a random number to dream teams. Questions a plenty, but I will start with a basic one...

- Are you just trying to determine the pick order for people to pick their team?

If so, do feel Access is the best tool to achieve this? What do you want Access to do for after obtaining the random numbers?

 

[MichaelRed]

draftOrder = Int((11 - 1 + 1) * Rnd + 1)?

but the part "(11 = 1 + 1)" is just a const (11), so why 'do the math'?

further, the process is hugely inefficient and will generally require MANY more random numbers to be generated than the required (11) as eachb 'failure' requires an additional value generation -and this can potentially occru numerous times for each element. I SUPPOSE that for the trivial hobbist it is un-important -at least for small collections.

A more efficient soloution would use a two dimensional array, assinging an incrementing integer to one element and ANY random number to the second and then (efficiently) sorting the array based on the random element. Because the non-random element is assigned to be a unique value in hte desired range, there are no duplicates. The random number element -as a sort key- provides the randomizing value, and doesn't need to be checked for duplication as it is only the index, not the actual value.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TOTCOM11]

To answer willir's question, yes I am just trying to determine the draft order. Once a value is calculated, I have a function call this value, which is then called by an update query. The update query is what actually appends these draft order numbers to the actual team.

Michael, I've never done a two-dimensional array. Could you show me an example so I know how to set it up? That would be great!

Thanks!
TOTCOM11

 

[MichaelRed]

..., 'the devil is in the details'. Fortunatly, these are available in the ubiquitous {F1} facility available with Ms. A. Keyword "Array", is one way to find the several topics.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places