[AccessD] Random numbers lottery-style

Steve,

Rather than an array how about using the VBA Collection object which will allow you to remove an item.  Add your Activity ID items to a Collection with your numeric value as the key value.

HTH,

Jim DeMarco

-----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


 



***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************