random records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to pull random records (non-inclusive membership numbers) from a query.
What criteria do I need in the query so that I can run the query from a form
that allows me to get records for example: memnumbers 1121,1243,1344,2172
etc?
As opposed to all records between 1121 and 2172...

Thanks
 
Hi,

SELECT TOP 5 *
FROM myTable
ORDER BY Rnd( NumericalFieldNameHere )



will return 5 random records. It is important to use a field name inside
Rnd(), else, the function would be called just ONCE, and used for all the
records. Having implied a field, the expression is evaluated for each
record, so each record get its random value. Ordering them and taking the
top 5 completes the trick.


Hoping it may help,
Vanderghast, Access MVP
 
I need to pull random records (non-inclusive membership numbers) from a query.
What criteria do I need in the query so that I can run the query from a form
that allows me to get records for example: memnumbers 1121,1243,1344,2172
etc?
As opposed to all records between 1121 and 2172...

Thanks
Click to expand...

One way is to shuffle the records in random order, and return the top
ten records (if you want to see ten randomly selected ones).

It helps to have a small VBA function to do this. Open a new Module,
and copy and paste this into it:

Public Function RandNum(vIgnore As Variant) As Double
Static IsRandomized As Boolean
If Not IsRandomized Then
Randomize ' Initialize the random number generator
IsRandomized = True ' but only the first time it's called
End If
RandNum = Rnd()
End Function

Then in your Query, include a calculated field:

Shuffle: RandNum([memnumber])

Sort by this field, and you'll present the members in scrambled order.

You need to pass a value to the RandNum function to force Access to
call it on every row. If you just try to sort by Rnd() it won't work,
since it will call Rnd() only once and repeat the value.

John W. Vinson[MVP]
 
Ok, "random" was a poor choice in wording. The records I need aren't truly
random. I meant not all records between any two; just a few select
(specific) records.

Thanks,
JD

John Vinson said:
I need to pull random records (non-inclusive membership numbers) from a query.
What criteria do I need in the query so that I can run the query from a form
that allows me to get records for example: memnumbers 1121,1243,1344,2172
etc?
As opposed to all records between 1121 and 2172...

Thanks
Click to expand...

One way is to shuffle the records in random order, and return the top
ten records (if you want to see ten randomly selected ones).

It helps to have a small VBA function to do this. Open a new Module,
and copy and paste this into it:

Public Function RandNum(vIgnore As Variant) As Double
Static IsRandomized As Boolean
If Not IsRandomized Then
Randomize ' Initialize the random number generator
IsRandomized = True ' but only the first time it's called
End If
RandNum = Rnd()
End Function

Then in your Query, include a calculated field:

Shuffle: RandNum([memnumber])

Sort by this field, and you'll present the members in scrambled order.

You need to pass a value to the RandNum function to force Access to
call it on every row. If you just try to sort by Rnd() it won't work,
since it will call Rnd() only once and repeat the value.

John W. Vinson[MVP]
Click to expand...
 
Ok, "random" was a poor choice in wording. The records I need aren't truly
random. I meant not all records between any two; just a few select
(specific) records.
Click to expand...

You can use a criterion such as

IN (3128, 3384, 3872, 4025)


John W. Vinson[MVP]
 
Back
Top