Random Selection of Records

[Keith Griffiths]

I'm trying to do a search under a set criteria followed by a selection of
random entries meeting this criteria. But I don't seem to be able to
achieve this.

The idea being to search on say subject and then select a random set of
records meeting that subject.

Any ideas or thought would be helpful. I'm using Access XP to try this out.

TIA

Keith

[Rick Rothstein]

> I'm trying to do a search under a set criteria followed by a selection of

random entries meeting this criteria. But I don't seem to be able to
achieve this.

The idea being to search on say subject and then select a random set of
records meeting that subject.

Any ideas or thought would be helpful. I'm using Access XP to try this

out.

I'm not a database person myself, so I'll leave the specifics of
implementing this idea to you. What I am thinking is to find out how many
records were return from you search, assign the record numbers to an array
of integers, shuffle that array of record numbers into a random order, and
then pick out the first X number of record numbers (you decide on how many
random entries you want) in order to index back into the set of records that
was returned from you search. For example, if your search returned, say,
record numbers 2, 19, 20, 39 and 50, then set up this array

Dim NumberOfReturne dRecords As Long
Dim RecNums() As Long
' Search your database and get the returned records
NumberOfReturne dRecords = <<RecordCount , 5 for this example>>
ReDim RecNums(1 To NumberOfReturne dRecords)
RecNums(1) = 2
RecNums(2) = 19
RecNums(3) = 20
RecNums(4) = 39
RecNums(5) = 50

then shuffle the contents around using the subroutine found at the end of
this message

RandomizeArray RecNums

and, finally, use the first, say, 3 entries (for this example) to obtain the
record numbers of 3 random items from your search set.

Rick - MVP

The following is a generalized "shuffling" routine that I've posted in the
past. Give it an array of elements and it will put them in random order and
return the randomized elements back in the original array that was passed to
it. It only visits *each* array element *once* so it is quick. The code
takes care of running the Randomize statement one time only (which is all
that is necessary).

Sub RandomizeArray( ArrayIn As Variant)
Dim X As Long
Dim RandomIndex As Long
Dim TempElement As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
If VarType(ArrayIn ) >= vbArray Then
For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1
RandomIndex = Int((X - LBound(ArrayIn) + 1) * _
Rnd + LBound(ArrayIn) )
TempElement = ArrayIn(RandomI ndex)
ArrayIn(RandomI ndex) = ArrayIn(X)
ArrayIn(X) = TempElement
Next
Else
'The passed argument was not an array
'Put error handler here, such as . . .
Beep
End If
End Sub

[Douglas J. Steele]

Check http://www.mvps.org/access/queries/qry0011.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Keith Griffiths" <gr************ *@hotmail.com> wrote in message
news:c3******** **@titan.btinte rnet.com...

I'm trying to do a search under a set criteria followed by a selection of
random entries meeting this criteria. But I don't seem to be able to
achieve this.

The idea being to search on say subject and then select a random set of
records meeting that subject.

Any ideas or thought would be helpful. I'm using Access XP to try this out.
TIA

Keith

[Randy Day]

Rick Rothstein wrote:

[snip]

then shuffle the contents around using the subroutine found at the end of
this message

RandomizeArray RecNums

and, finally, use the first, say, 3 entries (for this example) to obtain the
record numbers of 3 random items from your search set.

Rick - MVP

[snip]

It's also possible to randomly pick from an
array without shuffling all the elements:

1) choose a random element 0..n and use it.
2) swap the nth element with the chosen element.
3) restrict the next choice to 0..n-1
4) repeat

As fast or faster than a full shuffle, and
you'd end up with a shuffled array after n
iterations anyway.

Just thought I'd throw that out for the group.

[Bob Quintal]

"Keith Griffiths" <gr************ *@hotmail.com> wrote in
news:c3******** **@titan.btinte rnet.com:

I'm trying to do a search under a set criteria followed by a
selection of random entries meeting this criteria. But I
don't seem to be able to achieve this.

The idea being to search on say subject and then select a
random set of records meeting that subject.

Any ideas or thought would be helpful. I'm using Access XP to
try this out.

TIA

Keith

Create an expression in your query grid with some numeric value (an
autonumber is fine) mod (rnd()* someconstant (I like 77)+1)

Expr1: CLng(nz([seniority],123456)) Mod ((Rnd()*17)+1)

Set the criteria for this expression to =1

It will return some subset of the other criteria.

Bob Q.