[AccessD] Random numbers lottery-style

Steve

How adding a check to see if the ActivityID is already in the selected list
- then cycle around and run the randomizer again until there's not a match?

Perry Harold

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Thursday, March 03, 2005 2:06 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Random numbers lottery-style


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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com