By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,536 Members | 2,165 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,536 IT Pros & Developers. It's quick & easy.

Random Selection of Records

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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 NumberOfReturnedRecords As Long
Dim RecNums() As Long
' Search your database and get the returned records
NumberOfReturnedRecords = <<RecordCount, 5 for this example>>
ReDim RecNums(1 To NumberOfReturnedRecords)
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(RandomIndex)
ArrayIn(RandomIndex) = 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
Jul 17 '05 #2

P: n/a
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.btinternet.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

Jul 17 '05 #3

P: n/a
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.
Jul 17 '05 #4

P: n/a
"Keith Griffiths" <gr*************@hotmail.com> wrote in
news:c3**********@titan.btinternet.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.

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.