Prev: error
Next: Iif Statement Problem
From: carl on 11 Mar 2010 09:02
I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

Thank you in advance.
From: Marshall Barton on 11 Mar 2010 11:23
carl wrote:

>I have a table named Table1 that has 200,000 records. Is there a way to
>randomly select 40,000 records from this table ?


In your code, use the Randomize statement before using the
query:

SELECT TOP 40000 *
FROM Table1
ORDER BY Rnd(1)

--
Marsh
MVP [MS Access]
From: John W. Vinson on 11 Mar 2010 11:51
On Thu, 11 Mar 2010 06:02:10 -0800, carl <carl(a)discussions.microsoft.com>
wrote:

>I have a table named Table1 that has 200,000 records. Is there a way to
>randomly select 40,000 records from this table ?
>
>Thank you in advance.

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]
From: Marshall Barton on 11 Mar 2010 13:33
John W. Vinson wrote:

>On Thu, 11 Mar 2010 06:02:10 -0800, carl <carl(a)discussions.microsoft.com>
>wrote:
>
>>I have a table named Table1 that has 200,000 records. Is there a way to
>>randomly select 40,000 records from this table ?
>>
>>Thank you in advance.
>
>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.


Good one, John. I completely forgot the part about Rnd
needing a field argument.

--
Marsh
MVP [MS Access]
From: carl on 12 Mar 2010 14:19
Thanks Marshall and John.
I am not an experienced Access person and have some trouble coming up with
the query that you recoomended. Here's what I have - it does not seem to
produce the result I need - a list of 40000 randomly selected items.

SELECT TOP 40000 RndNum([GroupName]) AS Shuffle, *
FROM Table1
ORDER BY Rnd(1);

I really appreciate your help.

"John W. Vinson" wrote:

> On Thu, 11 Mar 2010 06:02:10 -0800, carl <carl(a)discussions.microsoft.com>
> wrote:
>
> >I have a table named Table1 that has 200,000 records. Is there a way to
> >randomly select 40,000 records from this table ?
> >
> >Thank you in advance.
>
> 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]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: error
Next: Iif Statement Problem