[AccessD] Random numbers lottery-style

Hi Steve

The last part can be solved by a function and a query.
You set Picked to True when a record is drawn.

Calling Randomize outside the query (by an external function) takes
place in a different scope than that of the query, thus if Rnd(..) is
run in the query with the same seed initially it will, of course, return
the same sample or sequence of samples.

The solution is to generate the random number in the external
function:

Public Function RandomNumber( _
  Optional ByVal booRandomize As Boolean) _
  As Single

  Static booRandomized  As Boolean
  
  If booRandomize = True Or booRandomized = False Then
    ' A new seed is requested or this is the first run.
    Randomize
    booRandomized = True
  End If
  ' Generate and return a random number.
  RandomNumber = Rnd()

End Function

Now the query would look something like this:

  SELECT TOP n
    *
  FROM
    tblPick
  WHERE
    tblPick.Picked = False
  ORDER BY
    RandomNumber([ID] Is Null);

where n is the count of records in the requested sample, and ID is a
field that is never Null like the primary key. 
The use of ID in the parameter is needed to call RandomNumber not once
but for every record. 

/gustav


>>> erbachs at gmail.com 03-03-2005 20:06:13 >>>
Dear Group,

I'd like to fill up some test data tables in a Volunteer database that
tracks activities and the amount of time volunteers commit to these
activities.

I thought that I might attempt filling the tables in a random fashion,
but I'm having some difficulty. The table (tblVolActivity) I want to
fill has this structure:

VolActID - AutoNumber (Key)
VolunteerID - Long
ActivityDate - Date
ActivityID - Long
TimeSpent - Single

I've created another table (tblTempVols) that contains the following
info:

VolunteerID - Long
NoOfInterests - Long
NoOfTimes - Long

For each of the 300-odd volunteers I've used the Rnd function to fill
in the two 'NoOf' columns. The NoOfInterests is a number between 1 and
6, representing the number of different types of activity a Volunteer
commits time to. For example, transporting people, paying visits to
the sick, or working in the gift shop. There are a total of 29
different activities.

The NoOfTimes column shows the total number of times the Volunteer
worked during the year. That number is between 1 and 48. Again, I used
the Rnd function to fill this column as well as the NoOfInterests
column. So it looks like this:

VolunteerID - NoOfInterests - NoOfTimes
1 - 3 - 13
2 - 6 - 5
3 - 4 - 2
4 - 4 - 16
5 - 2 - 38
etc.

Now I want to fill another table (tblTempActList) that has this
structure:

VolunteerID - Long
ActivityID - Long

Here's where I'm stuck. The list of 29 different Activities has
ActivityIDs from 1 to 29 in an unbroken sequence. If a Volunteer is
interested in, say, 4 Interests (from the NoOfInterests column in
tblTempVols) I'd like to insert 4 rows for this Volunteer into this
tblTempActList table and randomly pick the ActivityIDs. Now, of
course, if I use the Rnd function to select 4 ActivityIDs there's a
chance that there will be duplicate ActivityIDs for this Volunteer. So
what I'd like to do is pick the ActivityIDs like they pick lottery
numbers; that is, removing the selected numbers from the pool before
picking the next one.

How do I accomplish that is the question. Does anybody have, say, a
PickLotteryNumber() function where the function would take a list of
numbers and pick one while removing the selected number from the list
of available numbers? I've been fooling with arrays, but I can't
"erase" an array element from the middle with a ReDim statement.

If this problem is solved then I can take the next step

Any ideas?
-- 
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com 
Security Page: www.swerbach.com/security