[AccessD] Random numbers lottery-style

Try this...

Function GetRandomSelections(nNumberOfSelections As Long, nSelectFrom As
Long) As Variant
Dim nSelection() As Long
Dim n As Long
Dim nHits As Long
Dim nRnd As Long
   ReDim nSelection(nSelectFrom)
   
   For n = LBound(nSelection) To UBound(nSelection)
      nSelection(n) = 0
   Next n

   nHits = 0
   Randomize

   While nHits < nNumberOfSelections
      nRnd = Int(Rnd() * nSelectFrom)
      If nSelection(nRnd) = 0 Then
         ' new number selected. Flag it as 'used'
         nSelection(nRnd) = 1
         nHits = nHits + 1
      End If
   Wend
   GetRandomSelections = nSelection()
End Function

Sub testRnd()
Dim vArray As Variant
Dim n As Long
Dim str As String
   vArray = GetRandomSelections(5, 29)
   For n = LBound(vArray) To UBound(vArray)
      If vArray(n) = 1 Then
         str = str & ", " & n
      End If
   Next n
   Debug.Print "Numbers chosen: " & str
End Sub

Lambert

> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP: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