[AccessD] Random numbers lottery-style

Neal Kling nkling at co.montgomery.ny.us
Thu Mar 3 13:50:30 CST 2005


This might do you.

Return a randomized list from your table something like this:

select tblActivities.* from tblActivities
order by rnd(isnull(tblActivities.ActivityID) * 0 + 1)

Then take the first 4 records (or however many) from the resulting recordset.


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



More information about the AccessD mailing list