Prev: make-table
Next: Instr parsing with a comma delimited string
From: QB on 30 Mar 2010 12:25
If I have a first query that returns 3000 records, is there a way to build a
second query based upon the first to pull a random sampling of say 225
records from it?

QB
From: Jerry Whittle on 30 Mar 2010 13:37
SELECT TOP 225 *
FROM QueryName
ORDER BY Rnd(IsNull(NumberFieldInQuery)*0+1);

It would work best if NumberFieldInQuery is the primary key field in the
original table. NumberFieldInQuery needs to be a number field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"QB" wrote:

> If I have a first query that returns 3000 records, is there a way to build a
> second query based upon the first to pull a random sampling of say 225
> records from it?
>
> QB
From: John W. Vinson on 30 Mar 2010 15:31
On Tue, 30 Mar 2010 09:25:01 -0700, QB <QB(a)discussions.microsoft.com> wrote:

>If I have a first query that returns 3000 records, is there a way to build a
>second query based upon the first to pull a random sampling of say 225
>records from it?
>
>QB
You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: make-table
Next: Instr parsing with a comma delimited string